asked on # 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

Tester.xlsx

Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Addressing inaccuracies in your current worksheet, I would make the following changes:

1) In J5, use:

=IF(Values_Entered,ROUND(-PMT(Interest_Rate/Num_Pmt_Per_Year,Loan_Years*Num_Pmt_Per_Year,Loan_Amount),2),"")

2) In F18, use:

=IF(Pay_Num<>"",Princ+Int,"")

Then copy down through the end of that column

3) In G18, use:

=IF(Pay_Num<>"",MIN(Scheduled_Monthly_Payment-Int+Extra_Pay,Loan_Amount),"")

4) In G19, use:

=IF(Pay_Num<>"",MIN(Scheduled_Monthly_Payment-Int+Extra_Pay,INDEX(End_Bal,Pay_Num-1)),"")

Then copy that down through end of column

5) In H18, use:

=IF(Pay_Num<>"",ROUND(Beg_Bal*Interest_Rate/Num_Pmt_Per_Year,2),"")

Then copy that down through end of column

But again, I urge you to check out my sample amortization schedule here.

1) In J5, use:

=IF(Values_Entered,ROUND(-

2) In F18, use:

=IF(Pay_Num<>"",Princ+Int,

Then copy down through the end of that column

3) In G18, use:

=IF(Pay_Num<>"",MIN(Schedu

4) In G19, use:

=IF(Pay_Num<>"",MIN(Schedu

Then copy that down through end of column

5) In H18, use:

=IF(Pay_Num<>"",ROUND(Beg_

Then copy that down through end of column

But again, I urge you to check out my sample amortization schedule here.

Chloesdad solution is what I needed. I only had to add an additional column with an actual paid date, changed the formula to use the new column and make a few additional adjustment and it's works great!

Just be aware of what Matthewspatrick posted regarding some errors in the calculation of the last payment.

Thanks

Delores_C,

You never clarified the exact rule to use in the daily interest calculation. Be advised that the approach ChloesDad used is simple interest. If what you need is compound interest with a daily compounding, then it's wrong.

Also, keep in mind that now you are mixing daily interest (unclear of it's simple or compound) with scheduled principal payments based on a monthly schedule. That's not quite kosher.

Lastly, while it is certainly within your discretion to do so, I was disappointed to not get even a token share of the points for pointing out--and, more importantly, fixing--some of your calculation errors. I spent a lot of time on getting that right.

Patrick

You never clarified the exact rule to use in the daily interest calculation. Be advised that the approach ChloesDad used is simple interest. If what you need is compound interest with a daily compounding, then it's wrong.

Also, keep in mind that now you are mixing daily interest (unclear of it's simple or compound) with scheduled principal payments based on a monthly schedule. That's not quite kosher.

Lastly, while it is certainly within your discretion to do so, I was disappointed to not get even a token share of the points for pointing out--and, more importantly, fixing--some of your calculation errors. I spent a lot of time on getting that right.

Patrick

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.