XIRR using multiple columns for data

camiarrobino

New Member
Joined
Aug 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi - I'm attempting to model out the IRR for a potential real estate investment. What I am trying to do is, in column T, for each row (a new month), yield the IRR based on all of the monthly cash flows so far in column I, based on a hypothetical ending value in column S. Essentially, what the IRR would be if I walked away from the property on that specific date. Obviously the date range is relatively straightforward (B3 : B_ ) but how can I tell the formula to use column S as the ending "value" input, without making a separate column for each specific scenario - see my attempt on the image
IRR.JPG
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,980
Office Version
  1. 2010
Platform
  1. Windows
@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
ABIRST
2Net CFHome ValueEquityIRR (not!)
319/1/2020-$956.33$450,000.00$1,919.61
4210/1/2020-$953.76$451,312.50$4,653.35
5311/1/2020-$951.18$452,628.83$7,392.54
6412/1/2020-$948.60$453,949.00$10,137.20104060.40%
751/1/2021-$946.02$455,273.01$12,887.3418995.66%
862/1/2021-$943.43$456,600.89$15,643.006743.58%
973/1/2021-$940.84$457,932.65$18,404.173631.44%
1084/1/2021-$938.25$459,268.28$21,170.882123.39%
1195/1/2021-$935.65$460,607.82$23,943.141434.53%
12106/1/2021-$933.05$461,951.25$26,720.981032.64%
13117/1/2021-$930.45$463,298.61$29,504.41798.49%
14128/1/2021-$927.84$464,649.90$32,293.44636.04%
15139/1/2021-$925.23$466,005.13$35,088.09523.71%
161410/1/2021-$922.62$467,364.31$37,888.39445.79%
171511/1/2021-$920.00$468,727.46$40,694.34383.86%
181612/1/2021-$917.38$470,092.58$43,505.96338.10%
Sheet1


(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.
 

camiarrobino

New Member
Joined
Aug 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thanks for the help - the formula tweak worked. In terms of the financial modeling advice, it's a bit of a unique situation - a family member is loaning me the 25% down payment and i am paying him back @ 0% over 10 years. so for all intents/purposes there is no up-front cost for me - because the 25% down payment is spread over 120 monthly payments with no interest. just for context i am letting him use the detached garage for storage @ no rent which is why he is doing that. but anyways, the net cash flow is just the estimated rental income based on what current tenants are paying, less the payment to the bank, payment to my family member (100% principal), property tax, water bill, homeowners insurance, etc (obviously some of these inputs are estimates). i know that assuming fixed price increase of the home value is unrealistic just haven't gotten around to modeling something more realistic yet. in terms of the monthly outflow decreasing, it's because i assumed a small rental increase (i think 2% annually) which exceeds the expenses increasing - obviously i know that monthly compounding is way unrealistic but again, haven't refined it that much. i do financial modeling for work & am studying for the CFA if you've heard of it so i'm plenty capable on the modeling side just needed help on the formula - thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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