Excel/Excel and Loan Payments
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.
ANSWER: Fairly straightforward I think
I'd put the following headings in place
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
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?
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!
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