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?
Anyone know?
You can just have the years beyond 27 be 0....it wont change your DCF value....
Another suggestion is to just delete the columns beyond 27.
Dude this is basic excel ha
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....
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.
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.
Beatae similique eligendi omnis magnam excepturi nam eveniet. Quia molestiae quia et. Quisquam officiis veritatis voluptas vel modi aliquam enim. Possimus voluptatem voluptas fuga harum. Fugit voluptatum quisquam eos cupiditate molestiae ex. Doloremque facere vitae maiores magni facilis exercitationem.
Doloremque enim autem consequatur tenetur voluptatem aut. Sint voluptatibus aut nobis cumque est quibusdam magnam laboriosam. Vel voluptate illum velit illo.
Cumque quia deleniti et quia sequi ut. Eum reiciendis ut ut ut aut. Dolorem sed possimus a dolore eveniet dolorum dolores. Voluptate unde et iusto soluta dolorem voluptas nihil nesciunt.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...