Interest only calculator with compounding feature

[Mark Cashin]

Hi
We are trying to make a comparison calculator for Reverse Mortgages.
Depending on the lending institution they calculate interest differently.
Wondering if anyone knows if there is an off-the-shelf calculator available?
Needed
- Interest-only
- Interest-only compounded daily (365 days)
- Interest-only compounded monthly (12 months)

The years of the term can vary from 1 year to 5 years

Thank you in advance.

 

[GlennUK]

Can you take a look at this and tell me if I'm on the right track:

Book1 (version 2).xlsb
	A	B
1	interest rate per annum	3.50%
2	period - years	2
3	amount	10,000
4
5	interest only compound annually	712.25
6	interest compound monthly	723.99
7	interest compound daily	725.05
Sheet3

Cell Formulas
Range	Formula
B5	B5	=B3*(1+B1)^B2-B3
B6	B6	=B3*(1+B1/12)^(12*B2)-B3
B7	B7	=B3*(1+B1/365)^(365*B2)-B3

 

[Mark Cashin]

Hi Glenn, thank you for helping out!
It seems the amounts are a little off seems to be off a little based on the banks calculator

I really appreciate the help looking at this it just seems to be a little off. But close.

example 1 = monthly compoundng.

daily compounding seems to be off a little based on the banks calculator

 

[Dave Patton]

T202202a.xlsm
	A	B	C	D	E
1			Annually	Monthly	Daily
2	Principal	150,000.00
3	Rate	5.39%
4	Term	5
5	Future Value		195,024.10	196,277.97	196,392.54
6	Interest		45,024.10	46,277.97	46,392.54
7
1b

Cell Formulas
Range	Formula
C5	C5	=FV(B3,5,0,-B2)
D5	D5	=FV(B3/12,5*12,0,-B2)
E5	E5	=FV(B3/365,5*365,0,-B2)
C6	C6	=C5-B2
D6	D6	=D5-B2
E6	E6	=E5-B2

 

[GlennUK]

T202202a.xlsm
	A	B	C	D	E
1			Annually	Monthly	Daily
2	Principal	150,000.00
3	Rate	5.39%
4	Term	5
5	Future Value		195,024.10	196,277.97	196,392.54
6	Interest		45,024.10	46,277.97	46,392.54
7
1b

Cell Formulas
Range	Formula
C5	C5	=FV(B3,5,0,-B2)
D5	D5	=FV(B3/12,5*12,0,-B2)
E5	E5	=FV(B3/365,5*365,0,-B2)
C6	C6	=C5-B2
D6	D6	=D5-B2
E6	E6	=E5-B2

That's the same set of results as mine - which Mark Cashin (OP) says is 45,725.55 according to the bank.

 

[GlennUK]

Hi Glenn, thank you for helping out!
It seems the amounts are a little off seems to be off a little based on the banks calculator

I really appreciate the help looking at this it just seems to be a little off. But close.

example 1 = monthly compoundng.

View attachment 57156

View attachment 57155

daily compounding seems to be off a little based on the banks calculator

View attachment 57158
View attachment 57157

Then there is some part of the calculation by the bank that is not being mentioned. I've checked my maths, and also the posting by Dave Patton confirms that my hand crafted formulas match the FV function of Excel - and I'm certain that is also correct.

 

[Mark Cashin]

I believe on is because it is semi-annually however I can't figure out the daily one.
The bank calculators do not seem to match exactly so it may be an issue with the bank's online calculator.
However, these will work for illustration purposes with a disclaimer.

Thank you Glenn & Dave for taking time to help out.

 

[Dave Patton]

You stated in post #6 " I believe on is because it is semi-annually however I can't figure out the daily one."

Please provide information on how the interest is calculated. A complete definition of the rate and the calculation.
One clear example and expected results would also help.

Are the banks located in Canada and use semi-annual with calculation?

 

[Dave Patton]

T202202a.xlsm
	A	B	C
1
2	Principal	150,000.00
3	Rate	5.39%	0.00444204
4	Term	5
5	Future Value		195,697.04
6	Interest		45,697.04
7
1b

Cell Formulas
Range	Formula
C3	C3	=(1+B3/2)^(2/12)-1
C5	C5	=FV(C3,5*12,0,-B2)
C6	C6	=C5-B2

 

[GlennUK]

I believe on is because it is semi-annually however I can't figure out the daily one.
The bank calculators do not seem to match exactly so it may be an issue with the bank's online calculator.
However, these will work for illustration purposes with a disclaimer.

Thank you Glenn & Dave for taking time to help out.

I believe the differences stem from the bank adding on an arrangement fee to the initial amount prior to calculations. I know that my bank did that when quoting to me, verbally, a long time ago.