Dear all,
This query relates to a real estate appraisal I am putting together in Excel. I want to construct a formula to calculate the length of time it will take for the interest on a debt to erode the expected profit from a scheme, where the interest on the debt is compounded monthly. The difficult part (for me) has been how to incorporate the compound element.
I found the following calculation for Profit Erosion (expressed in years) online. (Sorry (new to this) but I couldn't work out how to include brackets around the figures to the right of R1=Ln & R2=Ln):
So based on the above I constructed the following:
Result: 0.053568304
To finish I try to convert the result to years using the formula:
This spits out “0 Years and 1 Months”, which looks to be at least 4 years less then I was expecting.
Any advice, guidance or alternative solutions on how best to achieve this will be gratefully received.
Again, sorry for the poor formatting. The correctly formatted formula can be view on page 31 of this link.
Many thanks.
This query relates to a real estate appraisal I am putting together in Excel. I want to construct a formula to calculate the length of time it will take for the interest on a debt to erode the expected profit from a scheme, where the interest on the debt is compounded monthly. The difficult part (for me) has been how to incorporate the compound element.
I found the following calculation for Profit Erosion (expressed in years) online. (Sorry (new to this) but I couldn't work out how to include brackets around the figures to the right of R1=Ln & R2=Ln):
Profit Erosion = r1/r2/d
R1= Ln 1+ Total Profit
-(Total Costs)
R2= Ln 1+ i/100
d
Total Costs = Total project or phase costs, excluding interest
Ln = Log to basee i.e. natural logarithm
i = Interest rate or Manual finance rate for calculation of Profit Erosion if specified
d = Dividing factor for each compounding period option (Monthly 12, Quarterly 4, Annual 1)
So based on the above I constructed the following:
R1= Ln 1+ 1,500,000
-(4,200,000)
R2= Ln 1+ 7%/100
12
Result: 0.053568304
To finish I try to convert the result to years using the formula:
=TRUNC(Result/12)& " Years and "&ROUNDUP(Result-TRUNC(Result/12)*12,0)&" Months"
This spits out “0 Years and 1 Months”, which looks to be at least 4 years less then I was expecting.
Any advice, guidance or alternative solutions on how best to achieve this will be gratefully received.
Again, sorry for the poor formatting. The correctly formatted formula can be view on page 31 of this link.
Many thanks.