# 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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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

You are welcome.

Replies
1
Views
115
Replies
8
Views
308
Replies
2
Views
55
Replies
1
Views
143
Replies
6
Views
158

1,126,940
Messages
5,621,733
Members
415,853
Latest member
Newlife72

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