Predicting an IRR

billyshears

Board Regular
Joined
Aug 29, 2013
Messages
61
I am trying to solve for IRR.

If I want to achieve an IRR in any year of 9% and I have my cash flows but need to solve for sale equity, what should I use?

So if in year 4 of owning a property, I want to sell it to achieve a 9% IRR and I have my original equity number and my cash flows, how do I solve for the sale equity I need?

So for instance,
If my initial equity is $100 and I sent my goal IRR in Year 3 to be 10%, and Year 1 I get 9% on my money, Year 2 I get 9% on my money, how do I solve so that in Year 3, my total proceeds (including what I received in years 1 and 2) are equal to a 10% IRR?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
773
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
Using Microsoft Excel:



Finding the IRR using Excel is fairly straight forward.

1. First, type the initial cash flow into any cell on the spreadsheet. Keep in mind this initial investment has to be a negative number.

Using our original example, type -500 into the A1 cell of the spreadsheet.


2. Next, just like the calculator, you will type the subsequent cash flow values for each period into the cells directly under the initial investment amount. Following our example, type 100 into cell A2, 200 into cell A3, and 300 into cell A4.


3. Finally you are ready to calculate the IRR.


To instruct the Excel program to calculate IRR, type in the function command "=IRR(A1:A4)" into the A5 cell directly under all the values. When you hit the enter key, the IRR value, 8.2%, should be displayed in that cell.

This same procedure can be followed for any data set if the cash flow values are listed one after another in a column directly under the initial investment amount. You would then put the range of cells in between the parentheses of the IRR command function.
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
@billyshears.... You asked how to "solve for the sale equity" (last cash flow), not how to use the IRR function.

But your initial posting contains several contractions and ambiquities that make it difficult to offer a turnkey solution.

First you say that you want to sell "in year 4 [....] to achieve a 9% IRR".

Then you say that you set your "goal IRR in Year 3 to be 10%".

Which is it?

Also, you say that in Year 1 and 2, you "get 9% on my money".

What do you mean by "get"? That is, from what source: rent?

With a $100 investment, did you receive $9 in each of Year 1 and Year 2?

Or do you mean that the property value appreciates to a value of $109 in Year 1 and $118.81 in Year 2 (9% compounded)?

Or do you mean something else entirely?

It would be much easier if you simply tell us what the cash flows are (dollar amounts) in Year 1 and 2.

And again, tell us what those cash flows represent (e.g. rent) so that we can be sure that the cash flow is appropriate.

Ostensibly, with the initial investment and Year 1 and 2 cash flows in A1:A3, the required Year 3 cash flow for a 10% IRR is (in A4):

=-SUMPRODUCT(A1:A3/(1+10%)^{0;1;2})*(1+10%)^3

Confirm that =NPV(IRR(A1:A4),A1:A4) is zero or nearly so.

Caveat: You might use different separators for parameters and array constants. Be sure to use the row separator for the array constant {0;1;2}.
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
More-flexible implementation....

With the initial investment and Year 1 and 2 cash flows in B2:B4, the required Year 3 cash flow for a 10% IRR is (in B5):

=-SUMPRODUCT(B2:B4/(1+10%)^(ROW(B2:B4)-ROW(B2))) * (1+10%)^(ROW(B5)-ROW(B2))

Confirm that =NPV(IRR(B2:B5),B2:B5) is zero or nearly so.

Alternatively, with the initial investment and Year 1 and 2 cash flows in B2:D2, the formula is (in E2):

=-SUMPRODUCT(B2:D2/(1+10%)^(COLUMN(B2:D2)-COLUMN(B2))) * (1+10%)^(COLUMN(E2)-COLUMN(B2))

Confirm that =NPV(IRR(B2:E2),B2:E2) is zero or nearly so.

I moved the data in order to demonstrate the need for (usefulness of) -ROW(B2) or -COLUMN(B2) in the formulas.
 

Forum statistics

Threads
1,171,630
Messages
5,876,544
Members
433,199
Latest member
guerin47

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