amortizing loans

Yes capital is all paid back, no it's not capital / payments. If starts at £100 12% over 24 months, then £100 x 12%/12 = £1 of interest. The monthly repayment is £4.71, so £3.71 of capital is repaid leaving £96.29 of capital, next month interest is £0.9629 payment is still £4.71 so £3.75 of capital is repaid. The 24th month all capital has been repaid.

Yes it is possible to determine the capital outstanding at any point through the life of the loan. The Excel fv function can be used.

=-fv(12%/12, 12, -£4.71, £100, 0)

£52.95 capital outstanding after 12 months

Yes LTV does decrease. If value is constant, but value can change also over the period of the loan.

stevio
The payments are the sum of all capital and interest payments over the life of the loan divide by the number of payments (to give a nice neat even payment amount).
This can be calculated longhand by repeating ganymede 's calculation 24 times on the reducing capital amount (Yawn .... )

I use a spreadsheet based on excel's PMT function which gives me a breakdown of all payments (cap/int) and shows remaining balance and a few other stats as the loan amortises.

The payment schedules it spits out agree with the ABLrate payment schedules on my amortising loans, and my repayments received.

If you can use a .xlsx excel spreadsheet I'm happy to upload a copy for you to have a play with.

(attempting to save this as earlier excel versions leads the machine to announce that the functions used in the calculations are not backwards compatible but seems to work anyway, so I could do .xls too but can't be certain it won't screw up..............)

see also ABLrate amortising payments

The calculation for payments per month for £100 @ 12% over 24 months is

=12% * 100 / (12 * ( 1 - ( 1 + 12% / 12) ^ (-24)))

which gives £4.70734722... round to 2dp is £4.71

or you can use the excel function

=-pmt(12%/12,24,£100)

rate = 12%pa / 12 = 1% per month
nperiods = 24 months
pv = present value

Overall interest is

=24*£4.71 - £100

£12.976333... rounded to £12.98

If 12 months into payments then balance was £52.95 using £4.71

=12% * £52.95 / (12 * ( 1 - ( 1 + 12% / 12) ^ (-12)))

which is £4.70435678

if you know payment is £4.71 then balance is

£4.71 = balance *12% /(12 * ( 1 - ( 1 + 12% / 12) ^ (-12)))

balance = £4.71 * (12 * ( 1 - ( 1 + 12% / 12) ^ (-12))) / 12%

Using £4.71 gives £53 using £4.70434722 gives £52.98155768

so can also do

12% * £52.95 / (12 * ( 1 - ( 1 + 12% / 12) ^ (-12))) = 12% * 100 / (12 * ( 1 - ( 1 + 12% / 12) ^ (-24)))

£52.95 = £100 * (1 - 1.01^(-12))/(1-1.01^(-24))

That gives £52.98155768... the 0.03... difference is because of rounding £4.70734722.... to 2dp in calculating £52.95.

For LTV. Initial LTV = Value / Loan (Principle) x100 %

LTV at N months = Value / FV x100%

FV  = -fv(12%/12, N, pmt(12%/12, 24, Principle), Principle, 0)

N  =datedif(Start date, now(), "m")

balance today would be something like this in a single cell

FV = -fv(12%/12, datedif(Start date, now(), "m"), pmt(12%/12, 24, Principle), Principle, 0)

LVT for a single cell it would be something like this

LVT = Value / -fv(12%/12, datedif(Start date, now(), "m"), pmt(12%/12, 24, Principle), Principle, 0)

Thanks ganymede. stevio if you mail me I can drop you my spreadsheet