# Predicting an IRR

#### billyshears

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

#### billyshears

##### Board Regular
Thank you but that doesnt help as I need the excel sheet to do the calculations

#### Drrellik

##### Well-known Member
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
@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
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.

Replies
1
Views
102
Replies
0
Views
231
Replies
1
Views
114
Replies
11
Views
151
Replies
0
Views
60

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.

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