Solved

Calculate interest on a spreadsheet using Excel 2007

Posted on 2013-12-10
7
767 Views
Last Modified: 2013-12-11
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
0
Comment
Question by:Delores_C
  • 3
  • 2
  • 2
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39709875
You'll have to very, very explicitly describe how the interest rate should be calculated.  Typically for these types of loans, a payment is considered "on time" so long as it falls within a certain range of dates.

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.
0
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
ID: 39709891
The interest is calculated in this spreadsheet using simple interest

i.e. monthly rate = annual rate / 12
                             = 3.8% / 12 = 0.3167% per month

To calculate interest daily you need to divide the interest rate by 365 rather than 12 to give the daily interest.

I have added an extra column "Days since last Payment" to the spreadsheet and changed the interest to be calculated based on the number of days rather than 1 month.
Tester-with-daily-interest.xlsx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39709992
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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Delores_C
ID: 39710109
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!
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39710125
Just be aware of what Matthewspatrick posted regarding some errors in the calculation of the last payment.
0
 

Author Closing Comment

by:Delores_C
ID: 39710126
Thanks
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39712437
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
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article will show you how to use shortcut menus in the Access run-time environment.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now