Create simple mortgage monthly repayment calculator in Microsoft Excel for your property

If you would like to have a quick check on how much monthly repayment for the property you planning to purchase, you may create a simple mortgage calculator in Microsoft Excel to help you estimate the amount of repayment for your house loan.

 

Step 1. Enter the follow information in one column.

Basic information such as Property value amount, down-payment, loan value amount, BLR (base lending rate) which I just simply put interest rate, tenure of loan in years and months, and monthly payment.

 

Step 2. Enter the figures for your property.

For the down-payment, normally it’s 10% and it may varies accordingly you may enter the formula =B2*0.1 if 10% down-payment in Cell B3, then the cell will automatic calculate for you. If it’s 20% then replace the formula with =B2*0.2 in Cell B3, otherwise, you can just enter the number directly.

For Loan amount value, put the code =B2-B3 to let it automatic minus the loan amount for you in Cell B4.

Enter the percentage in BLR or interest rate for Cell B5. If enter plain number, it should be 0.044 for 4.4%. Otherwise, right-click the cell to update the cell format to percentage.

Loan period is pretty straight forward, just enter the value will do.

 

Step 3. Enter the PMT formula

Once complete step 2, enter the code =PMT(B5/12,B7,B4) in Cell B8 and then click enter.

 

Step 4. Done!

Tadaa~ it’s done. Now you may change the number for property value, BLR, tenure of loan and the monthly repayment will automatically estimated for you.

 


Related Post
Disqus Comments Loading...