I want to check that the monthly repayment figure quoted by the bank on a 20 year repayment mortgage will actually reduce the loan to £nil at the end of 20 years.
Interest is charged daily.
I have found two Excel templates on the Microsoft website. However, one calculates monthly interest the other fortnightly interest.
Any pointers/suggestions would be great.
P.S The interest rate is fixed.
The calculation is very simple :
This is an example of the rate of return of 100000 (currency)
100000 100000 (present value of the loan)
20 years 7300 days (number of times the interest
is calculated)
0.1 0.000261158 the rate of interest
-5,000.00 -13.70 the payment (yearly/daily)
PMT(I17,B2,B1) Normally the calculation is done yearly/monthly but if you wish to do it daily then :{this is the way excel formula is written}
1. take the root of the yearly rate like this: 1.1^(1/365)-1
(I assumed the rate of 10%)
2. multiply the number of the years by 365.
3. Now - the payment is daily , as the interest is daily , so multiply it by 30 to have the average monthly - 13.7*30
Normally in Excel you use the functions when the number of payment is in correlation to the interest calculation , or else you have to calculate the rate to be used in your calculations.
Voila!
A fellow accountant pointed me to this
Thanks for your help yoavdothan but that's way too complicated for me! I have read it and re-read it a dozen times but still don't know where to start.
Hi!
Try to read it again. I formatted the answer and also added some remarks - I hope that it is clearer now.
Yoav
Got an answer for 3CA? Would you like to comment on the posted answers, or vote for the one which you think is the best?
Sign up for a free account, or sign in (if you're already a member).
Other people asked questions on various topics, and are still waiting for answer. Would be great if you can take a sec and answer them
