Join 3,513 readers in helping fund MetaFilter (Hide)


How do I create an amortization table where my payments change every period?
March 29, 2012 7:18 AM   Subscribe

Is it possible to create an amortization schedule in Excel when the payment amount, the principal amount, and the interest amount each year is unique?

I'm trying to figure out how to update an existing amortization table I have for a 30-year semi-annual bond. The interest portion is simple. I take the 5.2% per year and apply it against the remaining balance on the bond.

It's the principal that's the tricky part. The original amortization schedule had increasing annual principal payments each year (e.g. $520K, $550K, $580K, $610K, $640K, $675K, $710K, etc.). If additional principal paydowns occur (and they seem to be occurring every year, though the amount of the paydown varies), the remaining principal payments need to be adjusted accordingly.

We've previously had an outside company prepare the adjusted amortization schedule for us, but we're trying to bring that work in-house and use Excel to keep track.

However, I've only been able to find amortization formulas that assume a principal payment that stays the same each period, the interest stays the same each period, or the total payment stays the same each period.

Is there even a way to use Excel in this manner or do we need to purchase actual amortization software? If it's the latter, then what software would you recommend?

Thanks in advance for any help/advice.
posted by lea724 to Computers & Internet (16 answers total)
 
Sure. You just explicitly model out the cash flows. Its easy. Just write the formulas and copy:paste them out. You just use the PMT() function to figure out the yearly coupon/payment with the PV as the face, and the FV as 0, then you calculate the interest for each time period and the residual is the amortization.
posted by JPD at 7:23 AM on March 29, 2012


Oh on re-read - just type in your payment schedule rather than using PMT to figure out what the payment is.
posted by JPD at 7:24 AM on March 29, 2012


The payment schedule changes every time the principal has an unscheduled paydown, though, since the interest would suddenly be different and my yearly amount would be less, so I wouldn't know what to type in. Unless I'm reading your response wrong...?
posted by lea724 at 7:28 AM on March 29, 2012


It recasts the whole loan? Is there a formula for how the repayments recast?

Just add another column for unplanned amortization payments and the date you received them, then write the interest calculation to take that into account.
posted by JPD at 7:32 AM on March 29, 2012


This sounds eminently solvable by Excel.

How did the previous company track the amortization? Did they use Excel? If so, use their models as your guide. If they did not use Excel, do you have a printout of their reporting for this bond? Use that printout as a guide.

On preview: JPD is correct.
posted by dfriedman at 7:37 AM on March 29, 2012


It recasts the whole loan?

I think it does, yes, because the length of the bond has to stay at 30 years, so the remaining principal payments need to be adjusted to reflect the fact that an unplanned principal paydown occurred.

Is there a formula for how the repayments recast?

That's what I'm thinking is the issue, but if there's a formula, I'm not aware of it. This is probably why we had this schedule prepared with an outside company; they know the intricacies of what the principal needs to be adjusted to whenever additional payments are made. I just didn't know if there was a way to magically get Excel to do this calculation if I don't know what the formula is.
posted by lea724 at 7:37 AM on March 29, 2012


In that case, you also need to recalculate after each based on the time of the loan remaining, using the new (post payment) principal.
posted by carmicha at 7:42 AM on March 29, 2012


I just didn't know if there was a way to magically get Excel to do this calculation if I don't know what the formula is.

Yes of course there is. I'm sure the people who were providing you the formula were doing exactly this.

If the tenor of the bond remains 30 years there has to be some formula for the amortization of it.
posted by JPD at 7:51 AM on March 29, 2012


It sounds like this bond's terms allow for prepayment of principal, i.e., that you (the borrower) can pay the principal down by an amount greater than what is specified in the amortization table.

If that is correct, think of this as a mortgage amortization table that allows accounts for prepayments. See here for one possible template.
posted by dfriedman at 7:51 AM on March 29, 2012


Can you post the existing amortization schedule on Google Docs or something?
posted by JPD at 7:52 AM on March 29, 2012


If that is correct,

If I'm understanding her correctly it isn't just the prepayments, its that the repayment schedule is not constant. The actual check for interest + principle they write every year would change even if they made no prepayments? Right?
posted by JPD at 7:54 AM on March 29, 2012


The actual check for interest + principle they write every year would change even if they made no prepayments? Right?

Yes, that's correct.
posted by lea724 at 8:03 AM on March 29, 2012


The missing piece of information needed to answer your question concerns how the bond terms ensure/stipulate that payments will be made for 30 years regardless of whether prepayments occur. The other two points--how to handle changing annual payment and additional prepayments--are solvable based on the answers above.
posted by carmicha at 8:53 AM on March 29, 2012


right - and that has to be laid out in the docs for the loan.
posted by JPD at 9:08 AM on March 29, 2012


how the bond terms ensure/stipulate that payments will be made for 30 years regardless of whether prepayments occur.

The bond documents only state that the principal and interest need to be in "substantially equal amounts (subject to rounding) over the remaining term of the bond."

The outside company has been adjusting the remaining principal amounts seemingly haphazardly, and I don't know if that's what their software just does, if it's a conscious decision on their part, if they're looking at bond documents I don't have access to, or what.
posted by lea724 at 9:11 AM on March 29, 2012


heh. That vagueness is a little weird, but its still something you can do in XLS. See if you can find the full docs - that literally has to have the formula behind the amortization in it. Otherwise legally it would be just too vague.
posted by JPD at 9:28 AM on March 29, 2012


« Older I'm heading out to Oregon in m...   |  Where can I find really large ... Newer »
This thread is closed to new comments.