Most Important Functions in Excel to Learn
What are some of the most important functions to know in Excel in order to become an expert at RE financial modeling. I've done all of the BIWS case studies so I've seen a lot of them, but I wanted to reach out and see if some of you guys with years of experience could offer any tips.
You don't really have to use any complicated functions. Basic stuff like INDEX(MATCH()), OFFSET, stacked logic logic statements (IF+AND+MAX/MIN) make up the majority of my models.
In RE it's more certain techniques that you have to master:
-You have to learn how to create a circular loop to capitalize your interest on a development deal. On the same note you need to be able to crank out a development costs tab that spreads certain line items over certain periods of the deal (some people use S curves but imo that's overkill).
-You have to know how to do sensitivities really well. Ideally you can run a scenario matrix with any amount of variables, e.g. such that scenario 1 = HC increase by 2%, rents stay flat, interest stays flat, lease-up pace = 18 units/mo ; scenario 2 = HC increase by 2%, rents increase by 1.5%, interest stays flat, lease-up pace = 18 units/mo and so on for every possible permutation. Then you format it into a matrix that shows whatever variables you're sensitizing around (IRR/EM/RoC/CoC whatever)
-You have to know how to crank out a waterfall. Not super complicated but definitely feels confusing the first time you do it.
-You need to be able to layer mezz debt into a cash flow.
-You need to be able to put together a sources and uses into every cash flow. This is important in development deals for running your draw schedule properly.
-You need to be able to put together a debt table using floating rates.
probably F1
Pretty accurate +1
This explains all of the key points well. Follow this guide and you'll be good.
sumif, countif, sumproduct/index&match, eomonth, yearfrac, min/max, and/if, XIRR
Interview Excel Exam...formulas?? (Originally Posted: 08/08/2016)
Hi Everyone, My friend currently works in PWM at a major firm as an Associate. He is basically an assistant but has learned a LOT about the industry. He has a second round interview with a global fund manager that services only institutional clients. Essentially he is making the switch from the retail side to the buy-side/institutional clients. The position title is 'Analyst, Global Investor Relations.' Obviously, most of the roles in IR would involve good communication skills, ability to multi task, etc. That is fine. However, he was told that on his second round, he would have to take a 2 hour test. A portion of the test would include "basic financial modeling" in Excel. One of his interviewers told him he should know how to calculate weighted average cost of capital (WACC). This was the only "clue" he got.
For someone coming from a PWM background (and not i-banking), what other formulas/concepts do you think could be tested? What other concepts are similar to WACC? Just asking in terms of preparation.
Thanks!
these, plus datedif, edate, indirect on occasion, vlookup, hlookup, etc.. understanding binary code is in my opinion important (i.e. formula * (Cell B$2>0); random question though--I've found some models XIRR returns a 0% which is wrong because when I take the (1+irr(....))^12-1, the correct result shows. Any idea why this works this way in some models and not others? I always though XIRR worked for everything...
XIRR always seems to work for me except in the case if the first value was positive. I had a case where money was coming out and paying for the costs later so it messed up.
If they're gonna ask for the WACC, good possibility they'll have a DCF problem with valuation of a company or project
Thanks for your insight, I appreciate it!
valuation is a fair question, like the guy said - DCF. Other than that I have no idea, know how to work a pivot table.
DCF seems to be the consensus. Consider learning how to pull the standard US GAAP sheets from Bloomberg into Excel, and calc. down from there.
Know how to use a Pivot Table, V LOOKUP etc and you should be fine.
Microsoft Excel Functions Help! (Originally Posted: 06/06/2017)
Hello WSO!
I'm currently modeling an economic index for a small city in America. Right now, we're running a sensitivity analysis with three different cases (think boom, recession, etc) I'm trying to count how many times each case was closest to the actual outcome, and how many times each case wasn't correct about the outcome. For example, if the growth rate was positive, a case predicted negative, that's incorrect. So basically I'm trying to figure out an excel formula that can determine how many times a case predicted the actual outcome, and how many times it was incorrect.
You could use an IF function followed by a COUNTIF function.
Use the IF function to sort the outcomes as either correct or incorrect:
=IF(predicted case>=0,IF(actual case>=0,"Correct", "Incorrect"),IF(predicted case0,IF(actual case0,"Correct", "Incorrect")))
Then use two COUNTIF functions to add up the total number of each:
=COUNTIF(range of the IF function, "Correct") =COUNTIF(range of the IF function, "Incorrect")
I'm sure there are lots of ways to do it, but this should work for you.
If you're trying to get more precise than that, you'll need to set a certain value that you want the prediction to be within to render it "Correct", so that you can input a range which the actual case must fall within relative to the prediction.
SB'd! Thank you!
One of the main function used for financial modeling / LBO is What if Analysis: Goal seek, Sensitivity/scenario and Data table.
Excel formula HELP! Any help appreciated! (Originally Posted: 10/30/2016)
So basically the situation is this.
I have a financial model with 2 scenarios and on the valuation sheet I have a cell ("C1") at the top where if I type "1", the numbers of Scenario 1 come up, And if i type 2, number of Scenario 2 come up
What I want is for the DCF and IRR portion of both to come up simultaneously side by side.
How do I go about it?
Can there be a sth which wuld give the output of scenario 2 on the same sheet side by side?
I mean wth like "IF( C1 had been 2)" - I mean making it up but i hope someone gets the gist
appreciate it
Adipisci aut a nihil ad minus aut. Est nisi dolores repellendus voluptas. Qui et aut et ullam debitis nisi. Voluptatibus sed occaecati sed incidunt quia quos sunt.
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...