Predicting an IRR

[billyshears]

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?

 

[Drrellik]

https://www.calculatestuff.com/financial/irr-calculator



us this to help

 

[billyshears]

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

 

[Drrellik]

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]

@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]

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.