Formula required for annual payment percentage increase on loan

[DuncanGilroy]

I need help with a formula in Excel to work out the annual percentage to increase payments for a loan.

So if a loan of £2,000,000 is given, the loan payment term is 15 years and the interest rate is 12%. The PMT would be £24,003.36 per month.

But what I need to do is allow the lender to pay only £15,000 per month for the first year and then increase this amount by a constant percentage on a yearly basis. At the end of the 15 year period the outstanding balance should be zero.

I have manually worked out that yearly increase of the payments should be 8.76... %. This will mean the amount owed will increase over the initial years but will be fully paid off over the full term. What I'm trying to work out is the Excel formula required to provide the 8.76% value. Using this would mean that if I change the initial loan amount or the term the annual percentage increase would automatically update to the correct value. Doing this will allow me to easily work out the payment schedule during price or term length negotiations.

Many thanks for any help in this as I have an idea of the formula but am struggling to bring it all together.

Thanks again
Duncan

 

[istiasztalos]

Hi Duncan,

There are a couple of built-in formulas in Excel, but those I checked require constant payment amounts. There may be a financially knowledgeable Excel guru here who can devise a formula, but I think using Solver here would be a lot easier.

I mocked up a quick table calculating the monthly payments with a variable input starting loan, starting monthly payment & yearly income in payments (you can add additional ones too, like in what year should payments increase, or if there's a ceiling for monthly payment amounts, etc...) then activated Solver from the Excel options and it took a second to find that your number would be exactly 8.76527605237879%