using excel for amortization schedule that varies

In the US, at least, though, it's common for secondary loans (e.g., a 2nd mortgage) to have significantly worse terms than primary loans (the first mortgage), even if both loans are fixed-rate.

You can sometimes benefit from splitting a loan into multiple parts.

In particular, be aware that variable rate mortgages and loans are much more varied than fixed-rate ones. Others have noted that in many cases, there's enough consistency to use a spreadsheet or even a simple direct (closed form equation) calculation. P(4) = (d + (1 + r)^4 (r s - d))/r = 952.40ĭoes anyone know if this formula is worth pursuing in a spreadsheet or things vary too much between banks to get a generalized solution? The interim periodic balances can be obtained with this formula p(x) = (d + (1 + r)^x (r s - d))/r Their example applied to the formula for d s = 4329.58 See also Calculating the Present Value of an Ordinary Annuity where they show If the APR is an effective annual rate use r = (1 + APR)^(1/12) - 1 to obtain the monthly rate.Īn expression can be obtained for the periodic payment d s = (d - d (1 + r)^-n)/r R is the periodic interest rate, so if the APR is a nominal annual rate compounded monthly r = APR/12. The summation can be converted to a formula by induction, so The mathematics on which the usual formula is based is that the sum of the payments d, each discounted to present value (PV) by 1/(1 + r)^k, should equal the initial (present value) value of the loan s.

Using excel for amortization schedule that varies