Mortgage Payments Calculator...

Originally Posted by mrexcel27

I am working on a mortgage payments calculator. UK mortgages are similar to US mortgages except they are usually taken over 25 years, not 30 and there is no monthly tax payments included (stamp duty is paid in full at the time of purchase, and council tax is a separate payment based on a historical valuation of your property.

The duration of the loan does not matter; it is just a variable in the calculation. Taxes and other add-ons charges (e.g. mortgage insurance, called PMI in the States) are not part of the loan calculation per se; but they might be added to the periodic loan payment at the end. Up-front fees (e.g. "stamp duty") might impact the advertised "APR"; but in that case, the "APR" should not be used as the annual interest rate for the purpose of loan calculations. The only rate that matters is the finance charge for the loan itself. In the States, that is called "interest rate", which is distinct from "APR". The "interest rate" and "APR" are the same if and only if the APR calculation does not include extraneous fees.

Another issue to consider is whether the advertised rate is a compounded or simple annual rate. In the UK, I believe the "APR" is a compounded rate, following EU rules. I don't know for sure about the (real) interest rate. In my dealings with other UK problem, including working with a professional lender, my understanding is that the (real) interest rate is a simple rate.

The point is: if the annual rate is a simple rate, the monthly rate is indeed annual/12. But if the annual rate is a compounded rate, the monthly rate is (1+annual)^(1/12)-1. TBD.

Originally Posted by mrexcel27

I have made a good start on this (file attached) - it is common in the UK to have a promotional interest rate apply at the start of fixed rate mortgages, and so my calculator attempts to account for this

The formula in K5 should be:

Please Login or Register  to view this content.

The FV expression is the remainder of the loan after the first F15 payments.

Originally Posted by mrexcel27

I would also like to add a formula to calculate the new duration of my mortgage that results from extra payments being made earlier on (months reduced) and any interest saved by making these extra payments.

Early, late and additional payments affect the amortization schedule, not the loan payment calculations. The contracted duration of the loan does not change.

It might help to simplify the amortized interest and principal payments. There is no need to use IPMT and PPMT; in fact, they give incorrect results since they cannot account for early, late and additional payments.

Ostensibly, the formulas should be:

Please Login or Register  to view this content.

I say "ostensibly" because these formulas should be enhanced to satisfy your other needs below.

Originally Posted by mrexcel27

Also I would like to show/hide the number of monthly payment rows automatically. [....] if I make extra payments over and above the fixed monthly payment, my number or months remaining should reduce instead of my monthly payment amount over the remaining months reducing.

All of these formulas require substantial, but simple changes in the amortization schedule. Unfortunately, they require more time and thought than I can invest at this time. In the general, the paradigm to use:

=IF(OR(prevPmtNum="",prevBal="",prevBal<=0),"",performCalculation)

Another detail to consider: real-world payments (e.g. K3 and K5) should be rounded to 2 decimal places (or less). That, as well as early, late and additional payments, will substantially impact the formulas to calculate the "last" payment amount.