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.
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.
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
posted by JPD at 7:24 AM on March 29, 2012
Response by poster: 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
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
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
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
Response by poster: 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
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
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
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
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
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
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
Response by poster: 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
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
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
posted by JPD at 9:08 AM on March 29, 2012
Response by poster: 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
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
posted by JPD at 9:28 AM on March 29, 2012
This thread is closed to new comments.
posted by JPD at 7:23 AM on March 29, 2012