I am currently building an amortisation schedule for a property mortgage and am having difficulty filling the time series according to the length of year (Number of Payments). Any help would be greatly appreciated.
I would like the period to update automatically based on a monthly payment spread over 30 years, but everything I see uses the fill function, Below is the Calculator Lay Out:
[TABLE="width: 1126"]
<tbody>[TR]
[TD]Loan Amortization Schedule[/TD]
[TD][/TD]
[TD]Period[/TD]
[TD]Date[/TD]
[TD]Beginning Balance[/TD]
[TD]Payment[/TD]
[TD]Interest[/TD]
[TD]Principal[/TD]
[TD]Ending Balance[/TD]
[/TR]
[TR]
[TD]Input[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]£376,000.00[/TD]
[TD]£1,366.16[/TD]
[TD="align: right"]£587.13[/TD]
[TD="align: right"]£779.03[/TD]
[TD="align: right"]£375,220.97[/TD]
[/TR]
[TR]
[TD]Purchase Price[/TD]
[TD="align: right"]£470,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deposit[/TD]
[TD="align: right"]20.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Loan Amount[/TD]
[TD="align: right"]£376,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Annual Interest Rate (Bank set interest rate)[/TD]
[TD="align: right"]1.89%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Length of Loan (Years, 30 max)[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Calculated Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monthly Interest Rate[/TD]
[TD="align: right"]0.156%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monthly Payment[/TD]
[TD="align: right"]£1,366.16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number of Payments/Year[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number of Payments[/TD]
[TD="align: right"]360[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Cost of Loan[/TD]
[TD="align: right"]£491,819.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Interest[/TD]
[TD="align: right"]£115,819.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like the period to update automatically based on a monthly payment spread over 30 years, but everything I see uses the fill function, Below is the Calculator Lay Out:
[TABLE="width: 1126"]
<tbody>[TR]
[TD]Loan Amortization Schedule[/TD]
[TD][/TD]
[TD]Period[/TD]
[TD]Date[/TD]
[TD]Beginning Balance[/TD]
[TD]Payment[/TD]
[TD]Interest[/TD]
[TD]Principal[/TD]
[TD]Ending Balance[/TD]
[/TR]
[TR]
[TD]Input[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]£376,000.00[/TD]
[TD]£1,366.16[/TD]
[TD="align: right"]£587.13[/TD]
[TD="align: right"]£779.03[/TD]
[TD="align: right"]£375,220.97[/TD]
[/TR]
[TR]
[TD]Purchase Price[/TD]
[TD="align: right"]£470,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deposit[/TD]
[TD="align: right"]20.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Loan Amount[/TD]
[TD="align: right"]£376,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Annual Interest Rate (Bank set interest rate)[/TD]
[TD="align: right"]1.89%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Length of Loan (Years, 30 max)[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Calculated Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monthly Interest Rate[/TD]
[TD="align: right"]0.156%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monthly Payment[/TD]
[TD="align: right"]£1,366.16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number of Payments/Year[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number of Payments[/TD]
[TD="align: right"]360[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Cost of Loan[/TD]
[TD="align: right"]£491,819.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Interest[/TD]
[TD="align: right"]£115,819.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]