Formula to calculate average interest rate on loan payments

Hello Everyone!

I have the below sample loan payment schedule in an excel doc. It automatically calculates monthly payment, interest, principal and loan balance.

Screen Shot 2013-06-19 at 11.38.04 PM.jpg

I'd like to calculate average interest rate in a way that allows me to manipulate the monthly payment. So, for example, the borrower would be able to pay back more in the beginning months, and less in the end months, but still pay back the same amount cumulatively. Does anyone know any formulas I can use or have any ideas?

Thanks!

well based on the table you know the Total interest paid to be 583893.38 so that divided by the number of pmts is the average interest paid, you also know that the total of payments is 1583893.38 so that divided by 240 is the average of payments so if someone pays 6600+2433=9033 per month for 240 months it would equal the total of payments + the total of interest of 2167920 with a bit of rounding error since i used 6600 instead of 6599.56 and 2433 instead of 2432.88 for my calcs

Originally Posted by babbeleme1

Hello Everyone!
I'd like to calculate average interest rate in a way that allows me to manipulate the monthly payment. So, for example, the borrower would be able to pay back more in the beginning months, and less in the end months, but still pay back the same amount cumulatively. Does anyone know any formulas I can use or have any ideas?
Thanks!

This would require a custom solution, what you are seeking is N different loan payments given N different interest rates at which the present value of the loan is the same as when i% interest is charged on an N payments in equivalent amounts