Hi,
I am trying to create a mortgage calculator that forecasts the number of months it will take to pay off the loan.
I have successfully done this for a basic calculation where the monthly payment doesn't change but what I need is a calculation that works for payments increasing over 5 years.
I have uploaded my spreadsheet to Dropbox at the following link as it might be easier to understand if you can see what I am doing:
https://www.dropbox.com/s/1fg47837cymjv0k/Payment Mortgage Calculator.xlsx?dl=0
The value in V6 is the one that I want to display the months it will take to pay off the loan.
I use the basic loan details in cells G5,G6 and G7 and the monthly repayment in V5.
I then need to run this calculation to determine the monthly payment for year one (B12):
This gives me the actual monthly payment. I then need to repeat these steps for years 2,3,4 and 5 in cells D12, F12, H12 and J12. So for example year five I would use the following formula:
The formula I am using to actually get the monthly payments forecast is:
Now I understand that to calculate a basic loan works but I need the repayments to increase year on year and for the total months for the repayment to reflect this.
Please can someone suggest a way around this problem or point me in the right direction?
I am trying to create a mortgage calculator that forecasts the number of months it will take to pay off the loan.
I have successfully done this for a basic calculation where the monthly payment doesn't change but what I need is a calculation that works for payments increasing over 5 years.
I have uploaded my spreadsheet to Dropbox at the following link as it might be easier to understand if you can see what I am doing:
https://www.dropbox.com/s/1fg47837cymjv0k/Payment Mortgage Calculator.xlsx?dl=0
The value in V6 is the one that I want to display the months it will take to pay off the loan.
I use the basic loan details in cells G5,G6 and G7 and the monthly repayment in V5.
I then need to run this calculation to determine the monthly payment for year one (B12):
Code:
$G$8+(($B$16*52/12)*$B$12)+$G$9
This gives me the actual monthly payment. I then need to repeat these steps for years 2,3,4 and 5 in cells D12, F12, H12 and J12. So for example year five I would use the following formula:
Code:
$G$8+(($J$16*52/12)*$J$12)+$G$9
The formula I am using to actually get the monthly payments forecast is:
Code:
=ROUNDUP(NPER(G6/12,V5,-G5),0)
Now I understand that to calculate a basic loan works but I need the repayments to increase year on year and for the total months for the repayment to reflect this.
Please can someone suggest a way around this problem or point me in the right direction?