Loan Amortization Formula

[Guest]

I have a problem that is more of a math problem than excel.
I want to show an annual loan interest calculation that is based on a
monthly amortization schedule.
The problem is that each month we repay some of the principal and then next
months interest is calculated on a different principal amount.
Illustrated I am imagining that this is how this would look assuming we
start with a balance of $1,000 and an monthly interest rate of r.

1,000*r+(1,000-((1,000*r))*r+(1,000-(1,000-((1,000*r))*r)...and so on

This seems like some sort of math series and I am wondering if this can be
simplified in a formula.

If anyone can help I would really appreciate it.
Thanks.

Neda

 

[Dana DeLouis]

I want to show an annual loan interest calculation that is based on a

monthly amortization schedule.

Hi. I had a hard time with this one. To check one's work, here is a loan
template with extra payments.

http://office.microsoft.com/en-us/templates/TC062062831033.aspx?AxInstalled=1

For a closed form equation, I assume you are making a constant "Extra
Payment."
If r is your monthly interest rate,
p is your mortgage payment plus a constant "Extra Payment",
n is a particular month, then the interest at that particular month is given
by:

(Loan*r-p)*(r+1)^(n-1)+p

If you would like to sum a 12 month period, and call that period a year
(y=1, 2, or 3...etc)
then perhaps this equation. Sorry, but I couldn't find a smaller equation.
:>(

((r + 2)*r*(r + 1)^2 + 1)*(r^2 + r + 1)*
((r + 2)*r + 2)*(r + 2)*((r + 3)*r + 3)*
(Loan*r - p)*(r + 1)^(12*(y - 1)) + 12*p;

The above equations seemed to check with sample data on the above loan
sheet.
You will have to make an adjustment near the end of the loan since the loan
balance gets to zero faster.

 

[Guest]

Thanks I will try the equations.
Neda

Hi. I had a hard time with this one. To check one's work, here is a loan
template with extra payments.

http://office.microsoft.com/en-us/templates/TC062062831033.aspx?AxInstalled=1

For a closed form equation, I assume you are making a constant "Extra
Payment."
If r is your monthly interest rate,
p is your mortgage payment plus a constant "Extra Payment",
n is a particular month, then the interest at that particular month is given
by:

(Loan*r-p)*(r+1)^(n-1)+p

If you would like to sum a 12 month period, and call that period a year
(y=1, 2, or 3...etc)
then perhaps this equation. Sorry, but I couldn't find a smaller equation.
:>(

((r + 2)*r*(r + 1)^2 + 1)*(r^2 + r + 1)*
((r + 2)*r + 2)*(r + 2)*((r + 3)*r + 3)*
(Loan*r - p)*(r + 1)^(12*(y - 1)) + 12*p;

The above equations seemed to check with sample data on the above loan
sheet.
You will have to make an adjustment near the end of the loan since the loan
balance gets to zero faster.