Way to calculate present value without interest rate

L

lordy888

New member
Joined
Jan 6, 2021
Messages
9
The information would be:
Monthly instalment of 12,278 will be paid to settle part of principal and interest.
Amount will be fully repaid after 36 months, implying the future value should be 0.
Payment is in arrear.

Is it possible to calculate the present value with these information.
The correct answer for the above should be $400,000 of PV and 0.55% of implicit IR.

Any idea for the calculation or excel formula would be much appreciated.
 
You cannot solve directly for the interest rated with more than 4 periodic payments. You can establish an iterative process that will get it as close as you like.

As far as MS Excel goes, you can use the Goal Seek button as your "iterative process".
 
Thank you.
So any advice on how to use Goal Seek to solve these?
As there would be 2 variables of PV and IR, while Goal Seek seems to cover 1 variable only.
 
Goal Seek? Sure. Set it up as if you know everything and then tell goal seek to find what you want.
There are many solutions with the PV missing. One interest rate for each PV. Are you sure there isn't more information? Not even a hint?
 
I found many solutions with PV.. e.g. FV, NPER, RATE.
I get your point that I should solve variable one by one. However, still cannot figure it out.. T_T
 
You are misusing the technical vocabulary. The future value will not be zero. You are mixing up a legal concept and a technical term in finance. The future value of a series of certain payments is the value that the series would have if the periodic payments were each reinvested immediately upon receipt at the same, risk-free interest rate. It is a calculation of practical utility to bankers and insurerors and financial planners despite the almost certain falsity of its premises.

I am writing this on my tablet and do not currently have access to my version of excel. I’ll give you an answer after coffee and breakfast.
 
OK. I tried your suggestion of using the RATE function in Excel. It gave me a NUM error. I shall try to figure out why, but the IRR function gave me an answer of approximately 0.0055008 as a monthly rate, which is accurate to 0.02 units of currency and converts to approximately 6.6% annually.

If you look up the IRR function, you will find it is cumbersome to use for such a simple problem. I'll try to figure out what I am doing wrong with the RATE function, which, as I explained yesterday I have not used before. Rate is usually disclosed as a matter of contract. But I am probably doing something stupid. I am 99.9% sure that internally within Excel the RATE function just uses the logic of the IRR function, which gives the correct answer.

EDIT: According to the Help entry on NUM errors generated by RATE, it gives a NUM error if it does not converge on an answer within a certain tolerance after 20 iterations. So, if you get a NUM error for rate and are sure you made no input errors, you will have to set up an IRR function to solve your problem. I confirmed the result of the IRR function, and as I said, it was accurate to within two pennies, which is an irrelevancy relative to 400000.
 
Last edited:
You must log in or register to reply here.
Top