Excel Loan Payment Amortization Schedule

[gacollege]

I have attached a loan payment schedule that will break out the interest and principle to apply to an outstanding loan balance. The only issue I have is that I tested it with a 20 year $ 39,300 note (see attached) and it did not zero out on the 240th payment. I have looked over it until I am dazed....... I know it is something simple.....

This will be a great sheet if problem is solved!!

[alansidman]

I think the issue is that PMT function assumes that you will amortize on a monthly basis (evenly) while in reality your spreadsheet is calculating interest on a daily basis. This causes a gap and extends the full amortization beyond the anticipated repayment period.

Alan

[shg]

It comes out a lot closer if you use the constant 365 for days in a year instead of the 356 you're using now.

[martindwilson]

i'm all for a shorter working year

[alansidman]

Great catch on the 356. I looked at the spreadsheet for 1/2 hour and never saw that. Dislexia does that sometimes

nala

[gacollege]

THANKS!!!!!!!!!!!!!!!!!!!!!!!! WOW ! The difference at the end will always be a small amount. I hope this spreadsheet is a benefit to everyone who needs one. I have seen so many that just calculates payments - few that break it down based on payment date and interest/principal payout. How we we mark this post as solved? Thanks again shg!!!

[shg]

How we we mark this post as solved?

We could take a few minutes to read the forum rules to learn how.