Calculate interest on a spreadsheet using Excel 2007
I have an amortization scheduled worksheet; I need to additional calculate the interest paid based on the date of payment. Some payment maybe made early or late, I need to calculate the interest paid based on the date and add it to the running balance. Can someone help? My spreadsheet is attached. Tester.xlsx
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel
Some other notes:
1) Your formula for total payment in Col F is wrong. For an easy illustration why, use $10,000 as the loan amount, 3.8% annual interest, a 1 year term, 12 payments per annum, and no extra payments.
Note that the 12th payment is listed as being $847.90. That is wrong: it should be $850.59, just like the others. Your total payment calculation is constraining the total payment at the remaining balance from the last period. That's wrong.
2) Since your last principal payment is referring to the total payment, that to is wrong.
3) Your calculations allow everything to be complete floating point, but typically for loans enumerated in US dollars transactions would be rounded, at least to the nearest penny, and sometimes higher increments.
You may want to have a look at my article here, with an example that shows how to do this with additional options for extra payments.