Profit Erosion Formula

Sucram1

New Member
Joined
Jan 9, 2017
Messages
21
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
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
Joined
Jan 9, 2017
Messages
21
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,431
Messages
5,624,753
Members
416,046
Latest member
Elliottj2121

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top