excel loan amort interest help URGENT!

djc225's picture
Rank: Baboon | 144

i'm doing a loan amort for $1,000,000 at 4% annually compounded monthly over 1 year.

why does the total interest turn out to be less that simple 4% on $1,000,000?

below is the payment number followed by start balance, payment, interest, principal reduction, then balance after payment
1.00 $1,000,000.00 ($85,183.79) $3,395.13 $81,788.66 $918,211.34
2.00 $918,211.34 ($85,183.79) $3,117.45 $82,066.34 $836,145.00
3.00 $836,145.00 ($85,183.79) $2,838.82 $82,344.97 $753,800.03
4.00 $753,800.03 ($85,183.79) $2,559.25 $82,624.54 $671,175.49
5.00 $671,175.49 ($85,183.79) $2,278.73 $82,905.06 $588,270.43
6.00 $588,270.43 ($85,183.79) $1,997.25 $83,186.53 $505,083.89
7.00 $505,083.89 ($85,183.79) $1,714.82 $83,468.96 $421,614.93
8.00 $421,614.93 ($85,183.79) $1,431.44 $83,752.35 $337,862.58
9.00 $337,862.58 ($85,183.79) $1,147.09 $84,036.70 $253,825.88
10.00 $253,825.88 ($85,183.79) $861.77 $84,322.02 $169,503.86
11.00 $169,503.86 ($85,183.79) $575.49 $84,608.30 $84,895.56
12.00 $84,895.56 ($85,183.79) $288.23 $84,895.56 $(0.00)

Comments (3)

Feb 2, 2009

this is because you're reducing your balance over the year... it's 4% per year but you never accrue the full 4% over the year as you're amortizing it...

Feb 2, 2009

thank you, I just figured that out too. too easy to miss the obvious

Feb 2, 2009
Comment