Profit Erosion Formula

[Sucram1]

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):

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.

 

[Tetra201]

Maybe this:

Cell A1 =LN(1+1500000/4200000)/LN(1+0.07/12)/12, returns 4.375
Cell A2 =TRUNC(A1)&" Years and "&ROUNDUP(MOD(A1,1)*12,0)&" Months", returns "4 Years and 5 Months"

 

[Sucram1]

Maybe this:

Cell A1 =LN(1+1500000/4200000)/LN(1+0.07/12)/12, returns 4.375
Cell A2 =TRUNC(A1)&" Years and "&ROUNDUP(MOD(A1,1)*12,0)&" Months", returns "4 Years and 5 Months"

That works a treat.

Thank you so much for you help, much appreciated.

 

[Tetra201]

You are welcome.