Trending Content - Real Estate Forum
Career Resources
WSO Free Modeling Series - Now Open Through
July 31, 2026
9 Free Financial Modeling Lessons
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
+Bonus: Get 27 financial modeling templates in swipe file
+Bonus: Get 27 financial modeling templates in swipe file
High Finance Applications
Guaranteed
Get at least one offer GUARANTEED
- 1:1 support
- 92% success rate
- 2,000+ mentor network
- Unlimited Live bootcamps
- AI for resume + LinkedIn
- Networking automation tools
- HireVue simulation tool
- Full course library
- Private Slack community
- 20hrs of mocks
- + much more...
(new value/old value) - 1 for calculating growth rates lul
Just a tip - parentheses are not needed due to order of operations, which is the whole advantage of writing it this way instead of (new - old) / old.
Index(match,match) all day, every day, twice a day.
Can you explain this function and what you use it for?
Finding specific values in a table where you don't necessarily know which column row the item is in. Similar to vlookup but a bit beefed up.
As an aside, I definitely got dinged at an interview for not knowing this. Then I entered the workforce and literally never encountered a situation where I needed it until ~2 years in.
If you used vlookup instead of index match in my organisation you would probably be fired.
Index match is also more dynamic. If you add columns into a table later, or change the order of columns the formula will not break.
Sum
Magical Vlookup& IF and Pivot tables
Pornhub.com
CapIQ tab -> productivity -> launch Seamless
CapIQ tab -> productivity -> launch Seamless
CapIQ tab -> productivity -> launch Seamless
Offset Count If(And If(And(Offset(Count
Can you explain when/why you'd used "If(And(Offset(Count" ? creating a dynamic waterfall scenario?
It's actually not properly sequenced.
I use If(And(Count(Offset a lot to be able to change debt structures, reversion period, and to adjust TI/LCs for downtime.
Using count just eliminates the need to number your periods at the top of the pro-forma by counting the dates instead and offset lets you toggle different scenarios listed on the summary tab.
Calling bullshit on the 4th one.. maybe once in a career lmao
New job, so I don't use it anymore, but used this all the time when I was in lending. Toggle loan term, refinancing cases, rates, etc. simultaneousy.
Countif
Sumproduct (and be able to use conditional sumproduct)
seconding this
other than that, either succumb to the firm preference with LOOKUPS or become exceedingly good at writing a vlookup/hlookup on the fly
It was mind-opening when I realized 90% of reports (at the firm) were done automatically feeding through other software. I feel bad for the guys I know at Morgan Stanley who have to write reports from excel all day every day.
Another man of culture I see... sumproduct is an incredibly versatile formula when dealing with large amounts of arrays
nesting conditional statements in sumproduct has been an eye opener. makes equity multiple waterfalls a breeze!
If you bust bust out sumproducts with conditions to people who didn't know that was a thing my god do they look at you like you're a wizard
edate and eomonth
*(insert condition here) As it saves me writing an if statement since I'm lazy
sumif...a lot
hlookup/hlookup....a lot
offset, pretty handy
date/eomonth...a lot
hlookup and count and match together, makes an array dynamic for when you add in rows or take out rows and still calculate from the same row.
floating arrays for data are pretty cool too.
Crazy nested if formulas...a lot
Lol, this thread brings me back to the Analyst days of REFing the shit out of a formula and fucking up the entire model then frantically trying to fix it before someone notices.
Haha yep, this is why iferror has become one of my favorite formulas. If you error check everything properly (especially circular references), it becomes pretty hard to key in a letter somewhere by accident and blow up the entire analysis.
Howeva!!...
IFERROR, if used on everything in the world, can slow a sheet and book down, especially if what's inside the () is cross-sheet referencing. I try to limit my IFERROR to things I know have the possibility to error out.
Market Share curves based on 5 inputs? Yes
Sum of segmented revenue streams? Nah
Might be a dumb question, but how do you get super proficient in utilizing these excel formulas? I'm currently in UG and have the REFM Modeling course. Am currently reading Linneman's textbook and taking some RE Finance and Investment Analysis at UCLA Extension. I also have my Argus Cert, but when reading this thread I feel like I am lacking in Excel.
sorry if stupid question, ive seen people mention this but ive never had this issue since im always saving multiple versions of a file im working on....e.g. v1, v2, v3 i dont work directly with debt or anything but i sit next to our Liquidity team and they never have such issues due to making multiple versions each time they make a change or put something through. why does this seem to be different in banking / re?
Everyone here has pretty much covered the most common and useful formulas so I will weigh in on a couple resources for those trying to refine their Excel skills:
Excel basics: https://excelexposure.com/</a">https://excelexposure.com/ is a great spot to start no matter what level of Excel you are at. He lays out a very easy to follow syllabus that is organized by ability level.
Real estate centric Excel skills: https://www.adventuresincre.com/re-modeling/</a">https://www.adventuresincre.com/re-modeling/ is an awesome (and one of few) real estate centric sites and once you have a firm grasp on basic Excel formulas this is a great resource for learning more real estate oriented Excel analysis.
Other modeling courses: there are a bunch of paid real estate modeling courses you can take that are publicized here, others can weigh in on which provide the most valuable
If you are entrepreneurial and self disciplined however the first two will give you a great basis for an analyst career.
SUMIF INDEX(Match(Match constantly SUMPRODUCT for cash flow roll-ups OFFSET
IF/Then and SUMPRODUCT
There is currently a battle in my company over the use of the SUBTOTAL command. It's the nerdiest thing ever and it cracks me up.
Team subtotal.
You seem to be a gentleman and a scholar, so I think I know where you stand.
Seconded on SUBTOTAL
when modelling from scratch sumproduct is life.
Don't need much more.
Maybe some sum ifs and minima.
I've been using indirect a lot lately. Has been an incredibly versatile tool in organizing a lot of crazy data
Once you get the hang of indirect and R1C1 style references, you realize it's such a powerful function.
SUMIF SUMPRODUCT IF then Nested formulas Date functions INDEX MATCH
If I’m pulling in cash flows for multiple months then always index match so I can just drag the formula across without changing column numbers. I’ll use VLookup if I’m just doing a simple look up for one value.
Carl+Z
large, small can be helpful for me too other than that, mostly index match, sumif, sumproduct
F2
=IFERROR(VLOOKUP(HOES,ANYWHERE,FALSE),"GET FADED")
I agree with most everything on here but I gotta be honest, if you think index is superior to v/hlookups in all instances you're doing it wrong.
I'm curious, when are the lookups better than index?
2 advantages of VLOOKUP beyond the obvious simpler syntax:
If switching between sheets, it's much easier/faster to track. With Index/Match you have to switch between the sheets 3 times, with VLOOKUP only once. Can be confusing. Especially when you have to combine more columns for search criteria and especially with new users.
If you have to fill in a large and same formatted database with a primary key index, it's much easier and you can copy it easier.
IMM is of course a better tool to use for standardized, repetitive models, but often it's too complex for a small lookup and search function.
I use VLOOKUP much more often, but like IMM for the heavy lifting modeling stuff we do.
Also, there are easy ways to get around the 'fixed' column/row reference and you can make that dynamic pretty easily, so if someone else goes in and deletes/adds rows to the table it won't cause an error.
For waterfalls, being proficient with MAX(MIN ()) and MIN(MAX()) is helpful
Something said this but Sumproduct(-- = incredible for summary charts
What do the hyphens represent?
Boolean operator to determine if a condition is true or false.
F1
Datedif
Excel is primitive. Step your game up folks and get on some real data analytics software :D
How many of you Excel guru's can operate without a mouse? I've heard you become light years quicker once you learn all the keyboard shortcuts and hotkeys.
Trying to decide if I want to put in the brain damage to learn or just keep using mouse.
I can use the keyboard for almost all functions. But there are some things that are just too annoying to do without a mouse like some formatting.
What formatting is quicker with a mouse?
Shortcuts probably save more time formatting than they do w/ anything else.
It's pretty simple - just start slamming that alt button.
The vast majority of my work time is in Excel, and I'd say the time saved working through keyboard shortcuts rather than a mouse amounts to a 3-4 hours a week. Definitely worth learning.
Don't try to memorize all the shortcuts at once, the way I've built up my memory is when I find myself reaching for my mouse frequently I'd force myself to find the shortcut for it and write it down on a sticky. I'd keep 7-8 in front of me until I had them memorized, then move onto the next bunch. It's easier to memorize them if you're actually using them rather than just memorizing a bunch of 3-character strings and trying to organize that mentally.
At this point, most of my shortcuts are muscle memory and when someone asks me what the shortcut is for "insert Excel function" it takes me a few seconds to remember the keys used.
Besides obvious ones like INDEX(MATCH), I use these ones a ton:
-INDIRECT -SUM(OFFSET()) -MID()/LEN() -CONCATENATE() -ISNUMBER()
concatenate is too long; just use ampersands in between your concatenation values
Big fan of SUM(OFFSET when running any kind of variance analysis file.
Alt H + B + H
This bottom borders be thicc
can you give a few more like this...super helpful
Some quick shortcuts I use all the time:
Ctrl + Shift + & = puts boarder around selected area
Ctrl + Shift + - = Erases all boarders in a selected area
Ctrl + Shift + $ = Aromatically puts numbers in Currency format (Although most like to use Accounting format without "$" sign, so Ctrl + 1 = brings up format window and just use Tab key to get to accounting and put in "none" for currency symbol and 0 decimals)
Ctrl + [ = shows exactly where cells are in a selected formula by highlighting them. I like it better than tracing dependents/precedents
Alt + H + 0/9 = increase or decrease decimals. I for some reason find myself using this a lot
Good comments above. Some of my favorites:
I use https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-…</a">SUMIFS for multi-conditional summations rather than array formulas since it seems faster. Similarly with its sister functions like https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74…</a">COUNTIFS and https://support.office.com/en-us/article/averageifs-function-48910c45-1…</a">AVERAGEIFS. Though, be careful with AVERAGEIFS since it is a simple average rather than a weighted average.
For other multi-conditional calculations that the aforementioned formulas can't handle, I use https://support.office.com/en-us/article/guidelines-and-examples-of-arr…</a">array formulas. While array formulas are great to get a lot of calculations done in one cell, I recommend using multiple columns to "show your work" for more complex calculations. This helps you diagnose issues and make your worksheets easier to understand.
Lastly, I use PivotTables all the time. While not a formula per se, they are extremely useful. The trick I use most frequently with PivotTables is calculating weighted averages. See https://www.extendoffice.com/documents/excel/4816-excel-pivot-table-wei…</a">here for how to use a Calculated Field to determine the weighted average within a PivotTable. Simple averages can cause misleading conclusions, so I generally calculate both a simple average and a weighted average since this helps reveal outliers that may be driving a divergence between the two averages.
I like commenting when I have long formulas using +N("COMMENT").
Also, recording macros and editing them a bit if you find yourself repeating something across many tabs.
I’m just an intern, so probably what I use is different. But I use a lot of vlookups, sumifs, if combined with and/or. I know that these aren’t formulas, but a lot of work with the shortcut keys.
IF() - mixed with AND()/OR() EDATE() SUMIF() INDEX/MATCH SUMPRODUCT DATEDIF - (MOD(DATEDIF)) - handy for quarterly/bi-annual payments IFERROR - careful with this one! DATE()-YEAR()-MONTH() NORMDIST() - for my s-curves MAX/MIN PMT() - for investment loan calcs
VLOOKUPS for finding information between spreadsheets and pulling out results based on emails/outreach HLOOKUPS for the exact same thing, just in different formats Conditional Formatting isn't a function, but the visualization it gives you is amazing when presenting data to coworkers.
Offset(match...match) Named ranges Pivots If/If(And Sumproduct Conditional formatting
Vlookups and Hlookups pale in comparison to Offset(Match..Match)
Alt -> H -> O -> I Alt - E - S - T Alt - E - S - V Alt - H - O - R
Cool formulas but you all are missing a crucial one: Dsum
Hey- if you have an upcoming test, the only formula you’ll need to know besides from basics on IPMT, and PPMT
Index(Match),Match(
INDIRECT
OFFSET
SUMIFS
COUNTIFS
Conditional sumproduct
SUM(INDEX(),MATCH,MATCH()):INDEX(MATCH,MATCH)
SUMIFS(INDEX,MATCH)
XLOOKUP XIRR XNPV SUM SUMIF COUNTIF ALT+F4 CTRL+ALT+Del
CUMPRINC is great. Calculates the cumulative principal amortized over a stated period (Beginning or End of Period). Rather than having to build out a full amortization table, you can knock it out with one formula.
=FV() does the same thing
Neque ut quaerat numquam minima et pariatur beatae et. Et et et quibusdam quasi rerum dignissimos.
Quasi unde asperiores itaque sequi rerum. Rerum vero sed ex. Saepe quia voluptatem quisquam et magni exercitationem. Incidunt excepturi sit sequi quas earum recusandae sint iure. Hic mollitia nulla quam reprehenderit expedita quo enim.
Officia voluptatem qui labore. Itaque dicta possimus commodi. Non reiciendis et aut voluptas sapiente. Qui ad eos quis aliquam voluptas vitae.
Facilis eum et similique quod. Et quod dolor repudiandae nostrum eum.
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...
Id explicabo deleniti omnis. Inventore quaerat praesentium qui omnis nihil consequatur. Iusto dolorem impedit molestiae ut. Nisi sit facilis ea aliquid aut qui.
Voluptate vitae perferendis deleniti sit laudantium rem quas. Dolorum quisquam eos non occaecati aspernatur. Aut et vel dolorem dolorem quam optio. Itaque sequi veniam facilis doloribus.
Distinctio recusandae laborum accusamus rerum aliquam natus iusto. Temporibus nesciunt delectus sequi natus.
Et totam occaecati nihil voluptas. Itaque accusantium blanditiis sed officiis molestiae. Tenetur iusto provident deleniti ad. Quos et reprehenderit et dignissimos beatae id.
Consequatur eum quasi exercitationem ut reprehenderit optio consequatur. Aspernatur qui exercitationem voluptatem eveniet laudantium quibusdam. Non temporibus veniam hic ratione. Explicabo inventore sunt fugit voluptas eum dolorem qui. Suscipit et consequuntur libero consequatur.
Qui temporibus nesciunt perspiciatis ut aut praesentium aut. Optio pariatur iusto distinctio aliquam doloribus perspiciatis. Quasi ipsam id nam aspernatur.
Occaecati quidem aspernatur neque est cum repellat deserunt. Est nesciunt facilis eligendi et tempora mollitia qui. Incidunt non aut modi beatae quos ad. Molestiae eum aspernatur sit ex eius a aut.
Enim modi et deserunt rerum. Quasi quam commodi ab nulla. Veritatis mollitia architecto non ut. Aperiam aut voluptas aut illum aspernatur sit totam.
Quaerat vitae nemo impedit pariatur qui eaque. Quasi atque sint quidem voluptatibus accusamus. Sunt dolorem quibusdam odit sint reprehenderit. Beatae est neque dolorem id aliquid.
Necessitatibus nesciunt omnis illo odit. Qui necessitatibus quia voluptas et. Beatae recusandae tempora ratione vitae. Est aliquid aut fuga delectus perspiciatis dolorem sequi. Enim voluptatibus possimus perspiciatis fugit similique totam.
Est ut itaque quod assumenda deserunt quia. Dolores aut ut assumenda quam tenetur quia. Voluptatem consectetur itaque quae sit voluptas cupiditate. Voluptate perferendis amet neque adipisci corporis quia quis sit. Delectus nihil dolore non id doloribus quia.
Eveniet nisi atque et id. Magni in sed dignissimos eum quas facere sit autem. Explicabo enim qui veritatis maiores. Sunt molestiae soluta ea accusamus itaque saepe placeat.