Amortisation schedule with fixed no. of regular payments, but includes one-off payments

[macca345]

Hi,

Can anyone assist with building a loan payment/amortisation table with a set number of repayment periods and with one-off scheduled repayments? i.e., the additional payments would reduce the amount of the regular payments, rather than reduce the no. of payments.

So the inputs would be:
- Amount borrowed
- Interest rate
- Loan period in months (i.e. fixed term)
- Start date of loan
- Amount of scheduled one-off repayment
- Date of scheduled one-off repayment


Thanks,
Brendan

[shg]

That's how a regular amortization table would works, no? You enter a payment amount, the table subtracts the interest and applies the balance to the principal.

[jeffreybrown]

Can anyone assist with building a loan payment/amortization table

You shouldn't have to reinvent the wheel... https://www.vertex42.com/ ...and search for amortization schedule

[macca345]

Thanks guys, but while I've tried to work with a number of pre-made amortisation schedules, thus far I haven't been able to find anything which specifically covers what I need.

What I'm after is an amortisation schedule which allows me to specify the number of months while also specifying a balloon payment which is not at the end of the term but where a date is selected for the payment. The only calculators I've found either have the balloon payment at the end, or the additional payments reduce the number of overall payments. So I want to be able to tell it that I want the term to cover, say 108 months, with a one-off payment of $200k in month 3, and have it calculate the regular monthly repayments for the entire period.

If there is something out there which can already do this, I'd appreciate if someone could point me in the right direction, or if someone could assist with modifying one of the existing templates to do so.

Perhaps I'm missing something obvious. I'll keep working at it.

Brendan