# Profit Erosion Formula

#### Sucram1

##### New Member
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Tetra201

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
You are welcome.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,632
Messages
5,838,473
Members
430,549
Latest member
jayjay2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back