@camiarrobino .... The formula that you are probably looking for should appear in the Formula Bar as follows:
{ =XIRR(IF(ROW(
$B$3:B6)<ROW(B6),
$I$3:I5, S6),
$B$3:B6)
}
The key to avoiding the #NUM error in this case is the
curly brackets around the entire formula.
You do
not type the curly brackets. Instead, type the formula without them, then press
ctrl+shift+Enter instead of just Enter in order to
array-enter the formula.
The
absolute references $B$3 and $I$3 are also necessary in order to allow you to copy the formula down the column
after you array-enter the formula into T6.
(Do
not select T6:T18, for example, type the formula, press ctrl+shift+Enter. That produces the
wrong results.)
------
Although that corrects the syntax errors, the results are probably still incorrect.
Book1 |
---|
|
---|
| A | B | | | | | | | I | | | | | | | | | R | S | T |
---|
2 | | | | | | | | | Net CF | | | | | | | | | Home Value | Equity | IRR (not!) |
---|
3 | 1 | 9/1/2020 | | | | | | | -$956.33 | | | | | | | | | $450,000.00 | $1,919.61 | |
---|
4 | 2 | 10/1/2020 | | | | | | | -$953.76 | | | | | | | | | $451,312.50 | $4,653.35 | |
---|
5 | 3 | 11/1/2020 | | | | | | | -$951.18 | | | | | | | | | $452,628.83 | $7,392.54 | |
---|
6 | 4 | 12/1/2020 | | | | | | | -$948.60 | | | | | | | | | $453,949.00 | $10,137.20 | 104060.40% |
---|
7 | 5 | 1/1/2021 | | | | | | | -$946.02 | | | | | | | | | $455,273.01 | $12,887.34 | 18995.66% |
---|
8 | 6 | 2/1/2021 | | | | | | | -$943.43 | | | | | | | | | $456,600.89 | $15,643.00 | 6743.58% |
---|
9 | 7 | 3/1/2021 | | | | | | | -$940.84 | | | | | | | | | $457,932.65 | $18,404.17 | 3631.44% |
---|
10 | 8 | 4/1/2021 | | | | | | | -$938.25 | | | | | | | | | $459,268.28 | $21,170.88 | 2123.39% |
---|
11 | 9 | 5/1/2021 | | | | | | | -$935.65 | | | | | | | | | $460,607.82 | $23,943.14 | 1434.53% |
---|
12 | 10 | 6/1/2021 | | | | | | | -$933.05 | | | | | | | | | $461,951.25 | $26,720.98 | 1032.64% |
---|
13 | 11 | 7/1/2021 | | | | | | | -$930.45 | | | | | | | | | $463,298.61 | $29,504.41 | 798.49% |
---|
14 | 12 | 8/1/2021 | | | | | | | -$927.84 | | | | | | | | | $464,649.90 | $32,293.44 | 636.04% |
---|
15 | 13 | 9/1/2021 | | | | | | | -$925.23 | | | | | | | | | $466,005.13 | $35,088.09 | 523.71% |
---|
16 | 14 | 10/1/2021 | | | | | | | -$922.62 | | | | | | | | | $467,364.31 | $37,888.39 | 445.79% |
---|
17 | 15 | 11/1/2021 | | | | | | | -$920.00 | | | | | | | | | $468,727.46 | $40,694.34 | 383.86% |
---|
18 | 16 | 12/1/2021 | | | | | | | -$917.38 | | | | | | | | | $470,092.58 | $43,505.96 | 338.10% |
---|
|
---|
(Select each cell in column T to see the formula.)
GIGO!
I suspect that your cash flow model is not correct.
For example, it is not likely that the home value increases by a
fixed monthly rate of about 0.2917%. "Home value" varies with the market. Moreover, "home value"
per se is probably irrelevant.
Also, it is unclear why the net cash flows increase by the
repeated sequence(?) 0.0007% or 0.0008%, 0.0007% or 0.0008%, and 0.0018% or 0.0019%. (The variation is probably due to rounding.)
But more importantly, it is unclear what the
source of the net cash flows is, and what they are
"net" of. If the "gross" cash flows are principal plus interest, the "gross" cash flows might be the correct ones to use in the IRR model.
More to the point, it might help if you described the financial arrangement.
For example, did you start with a loan (price minus down payment) of $450,000, make regular (or variable?) payments monthly, resulting in a build-up of equity -- the principal part of each payment?
In that case, it would help if you explained the terms of the loan (loan amount, number of payments, fixed interest rate (or assumptions of a variable interest rate), and any "balloon payment", if the final ending loan value is not zero.
But that is only an example, based on the wild guess that this might be a loan. The financial arrangement might be something very different.
PS.... And please use XL2BB to post your data and formulas. It makes it easier for us to respond.