You are here:

Excel/Excel and Loan Payments

Advertisement

Question
QUESTION: I have an interest only loan on a HELOC. I would like to pay it down periodically but I am not sure how much principal I can pay periodically.

I would like to have an excel model that will determine the interest amount on the principal and then readjust each time I pay down the principal.

For example, the total debt is \$20,000 payable over 7 years with a balloon payment due at the end for the remaining principal. For the first payment, the interest-only portion of the debt is \$100 and I pay \$200. Therefore the debt is now \$19,900. For the second payment, the interest-only portion is \$90 and I pay \$150 or \$60 in principal...and so on. I want to figure out what the interest only portion each time after the principal is adjusted.

Thanks,

Chris

ANSWER: Fairly straightforward I think

I'd put the following headings in place
Date
Capital
Interest
Amount Paid

In the first row, the capital is the initial capital (\$20,000).  The interest is a formula

=capital * interestrate

(replace my words with cell references, or values)
The amount paid is typed (\$200)

In the second and subsequent rows, capital becomes a formula

=PreviousCapital + PreviousInterest - PreviousAmountPaid

The interest formula is carried down from the first row, and assuming you want to keep the payments constant, the payment is a formula

=PreviousAmountPaid

Fill this down as much as you need and you will have a working model.  You can overwrite any payment formulas with values if you want to alter them at any time.

---------- FOLLOW-UP ----------

QUESTION: Is there a way to project what the interest payment will be based on the new principal amount? For example, if the principal is \$20,000 and the first payment requires an interest due on the first payment is \$100 and the payment is \$200, the new principal for the second period is \$19,900. What would be the second payment provided the interest rate is 4% annually and it is compounded daily?

Thanks,

Chris

Answer
it is doable - my financial skills may not be up to the task though!  different people seem to use different methods to get the interest rates - this page http://superuser.com/questions/352981/excel-formula-to-convert-per-annum-interes explains how to get a daily rate - as it's a daily rate, you can get the number of days by subtracting the previous payment date from the current one.  Excel does come with a lot of financial functions, though I've never used them!
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Aidan: Thanks for your help. You have definitely put me on the right path. Take care, Chris

• Add to this Answer
• Ask a Question
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 About.com. All rights reserved.

Browse Answers: