Equity Lifetime Mortgage Interest - Excel formula

equityone

I have a Equity Lifetime Mortgage and want to calculate with the fixed interest daily, what would the total repayment would be year by year on the drawdown amount in future years.

I have used a excel compound formula but each month/year shows an error when comparing with yearly statements from the lender

Formula used:
=B6*((1+C6/100)/1)^(D6*1)
Amount  : B6
Interest   : C6
No of yrs : D6

MWT

Looks like you are mixing up annual interest and daily interest.

Try this:

FV = P * (1 + r/n)^(n*t): 

• FV: The future value of the investment
• P: The initial principal or investment amount
• r: The annual interest rate, expressed as a decimal
• n: The number of compounding periods per year
• t: The number of years the investment is held

The compounding period and interest rate must be simultaneous. For example, if the interest rate is given as an annual rate, the compounding period must also be in years.

equityone

thanks,

The lender states they calculate interest daily.

Cant get your formula  to work using excel.
Using the FV function assumes I'm making payments over a fixed period, which I'm not. Final payment would be at the end of my lifetime, whenever that maybe in the future. unless I won the lottery and repaid the equity loan early.

MWT

It is essentially the same as your formula just laid out more clearly.

This is my version...

=C4*(1+D4/E4)^(E4*F4)

C4 = Principal = 100

D4 = Interest = 0.05

E4 = Periods = 365

F4 = Years = 1

Result = 105.1267

So £100 compounded daily at 5% P.A.  gives you £105.13 a year later...

Try the calculator here: https://www.thecalculatorsite.com/finance/calculators/compoundinterestcalculator.php

equityone

Thanks again,

The calculator gives the same results as my calculator, but with still small yearly discrepancies from my yearly statements.

The small amount of error from both my and online calculator I think is acceptable to forecast.

thanks

MWT

The small yearly variation is probably just down to the number of decimal places they use before rounding or truncating the daily calculations.

TrickyDicky101

What are you using for the compounding periods?  Calculating interest daily does not mean compounding daily - indeed this would be somewhat unusual.  Much more likely to be 12 periods per year (ie monthly).  You need to ensure you are not taking an APR from your mortgage documentation when performing the calculation - you need the annualised nominal rate.