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?
posted by lea724 to Computers & Internet (16 answers total)
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.