Convert Fixed Rate Loan Spreadsheet for specific situation


Hi - I have Teach Excel's fixed rate loan spreadsheet that works perfectly for a fixed rate student loan that I have - it works with the interest rate divided by 12 - not with the calculation for an "effective annual interest rate". But one of my Student loans does not work in the sheet - neither with the rate divided by 12, nor with =((1+B4)^(1/12))-1, so do you know what IF statements and PMT statements and IPMT statements I need to change for this to work: Current Balance: $15,180.12, Last payment date: April 3, 2029; Current interest rate (variable): 6.500%, Current minimum payment: $157.23



Selected Answer

Frankly, I was hoping that I would come to understand your complaint as I was delving deeper into your worksheet but the enlightening moment didn't come. I don't understand "If the rate was semi-annual, divide by 2 instead of 12 for 12 months", nor your formula for "Effective Annual Interest Rate", nor why the effective rate should be used in this calculation: the calculation requires the applied rate, not the effective one. All of this by way of seeking your pardon if the solution I came up with should fail to do what you were hoping for.

In the attached workbook I have added a second sheet which is a copy of yours but most of the formulas changed. You have fields now (B6:B7) where you can enter start and end dates for the repayment schedule and a drop-down now in B5 where you can select the schedule (monthly, quarterly etc). I have applied a numbering system for the instalments which is no longer reliant on the number 12 (for monthly payments) and due dates for each instalment. Yes, and the calculation of the term (B8) is automatic, derived from the start and end dates.

I didn't find any errors in the old sheet. The new one just offers more flexibility. I hope that it will address your question in some way :-)



Thank you!
Allison (rep: 6) Sep 3, '18 at 9:15 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login