blog.marccus.net

Live. Life is Wonderful.





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.

Monthly repayment code - step 1 - blog.marccus.net

 

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.

Monthly repayment code - step 2 - blog.marccus.net

 

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.

Monthly repayment code - step 3 - blog.marccus.net

 

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.

Monthly repayment code - step 4 - blog.marccus.net

 


 




written by marccus.   category: Tech Tips.   Total 449 views    

 



Thanks for your reply!
谢谢你的留言

Comment section

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of


Comment section

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of




Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Pin It on Pinterest