How to make an Excel DCF model dynamic based on the number of forecasted years?

I'm currently interning at a real estate private equity firm and I have to build a model to analyse the DCF value of potential investments. My model is good, but different types of real estate do have different life spans. For every real estate property with a different life span I have to build a new model. I would like to have one dynamic model which suits all life spans.

How my current model looks:
* On top you input the variables like cost of equity, income through rent, etc
* Horizontally there are years. Now I have 36 years, but for my next model I need 27.
* Vertically there is a profit & loss statement, a balance statement, a cashflow statement and a DCF statement.

What I want to achieve is that on top you have a variable: "life span" where you can put for example "27" and then automatically it adapts the number of forecasted years horizontally.

Is there an Excel formula to achieve such a thing?

Comments (7)

Jul 24, 2018

Anyone know?

Jul 24, 2018

You can just have the years beyond 27 be 0....it wont change your DCF value....

Jul 24, 2018

Another suggestion is to just delete the columns beyond 27.

Dude this is basic excel ha

Jul 24, 2018

Have an input field for how many years to include (27 in your case). In your CF row, do if (current year of the cell> "years to include" (27 in your case), 0, otherwise your previous/original formula).
You can use that and toggle for next model.

On something separate, this is odd since you are not including all the future CF....

Jul 24, 2018

How I would do this:

Have input fields for start year (ie. 2018), how many years to include (27) and exit year (ie. 2045). Use one row for binary help cells. If (year of the cell) > ("exit year" + 1) than 0, else 1. The reason I would go ("exit year" + 1) is because we need an extra year to calculate the terminal value.

Multiply all your cash flows with the help cells (1 / 0). Cash flows that come after the exit year will be 0 (use number format to avoid zeros: "#,##0;(#,##0);-"). For terminal value use something like If (year of the cell) = exit year than (next year NOI) / exit yield.

Learn More

Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.

Jul 24, 2018

If(and(date conditions),TRUE,0) for the body of the spreadsheet? Or if you're more familiar with Excel formula*(And(date conditions) to save yourself some time. You can put this in each line or in a "live period row" as bodacious suggested.

Not sure I'm really understanding your question, I appreciate you're an intern but if you've managed to build a working model this should already be something that you are doing?

As for the dates, just build the cash flow for more years than you'll ever need, ie if you've got 36 years build one for 50, the extra months and years will zero out anyway if you've built your model correctly. Don't bother creating a if(date>max date, then 0) formula for the date. It serves no extra purpose, The less redundant/complex formulas you have in your spreadsheet the better, you're already going to have to drag the cell to year 50 or 36 whatever the situation, so why make the formula more complex than eomonth to if(date>date then)

If instead you're asking how to put two assets in one cash flow then don't. you're better off doing the following:

Create identical tabs for each asset you need
Create two "portfolio tabs" named > and < or whatever you want.
Create a portfolio sheet that is identical to the asset sheets. Keep the asset sheets in between the >< tabs. In your portfolio sheet use =sum('>:<'! Cell reference) to pick up all cells in that cell across the whole portfolio. Eg =sum('>:<'! A1) would give you A1 for every cell in between ><. This enables you to drag and drop assets into your portfolio easily without updating the formula.

The last bit was probably redundant but not 100% sure what you asked is what you mean.

Jul 24, 2018