Modeling Best Practices: =LET function
Curious if people are generally familiar or have begun to adopt the =Let function in excel for financial modeling. The syntax is extremely helpful for breaking down long, complex formulas into digestible pieces - making them far easier to both write and read.
The syntax is =LET(name1, value1, [name2, value2, ...], calculation), allowing you to assign names to various sub-calculations within the formula - keeping them dynamic and alleviating the need to name a bunch of cells - and then essentially spell out your final calc in words rather than a cluster of nested functions.
Basic example for calculating an LTV:
=LET(Fwd12NOI,sum(a1:a12),CapRate,$b$1,LoanBal,$c$1,LoanBal/(Fwd12NOI/CapRate))
Obviously gets more useful the more complex the named items are to calculate. Highly recommend getting familiar for anyone who hasn't tried.
Any new or less frequently used tricks that you've found that feel like a cheat code while modeling?
The =LET function is indeed a game-changer for financial modeling, especially when dealing with complex formulas. By allowing users to define variables directly within the formula, it not only improves readability but also reduces the risk of errors associated with referencing multiple cells. This function is particularly useful for financial modeling scenarios where clarity and dynamic calculations are critical.
Here’s why =LET is a standout tool for financial modeling:
Example Use Case:
Your example of calculating Loan-to-Value (LTV) is a perfect demonstration:
=LET(Fwd12NOI, SUM(A1:A12), CapRate, $B$1, LoanBal, $C$1, LoanBal / (Fwd12NOI / CapRate))This formula assigns: - Fwd12NOI as the sum of forward 12 months of NOI, - CapRate as the capitalization rate, - LoanBal as the loan balance, and then calculates the LTV in a clear and structured manner.
Other Tricks That Feel Like Cheat Codes:
For anyone diving deeper into financial modeling, adopting =LET and other advanced Excel functions can significantly enhance both efficiency and accuracy. If you’re looking for more structured learning, the WSO Elite Package offers lessons on Excel modeling, including templates and best practices.
Sources: EXCEL cheats megathread, EXCEL cheats megathread, Excel Formulas you use constantly on the job?, Excel Office Add-Ins, What additional Excel functions do you wish for?
This is awesome thanks for sharing
Not bad use of LET function, but I do think simplicity and practicality outweigh complexity. Breaking up the LTV into sub-calcs for the Fwd NOI and the Cap Rate build up can make it easier to audit.
I think overly complex or bulky formulas are great for reports where the formula doesn't need to be touched or edited since it serves a single function (most common in FP&A or operational process tasks), but in financial modelling, breaking up complex formulas allows for easier traceability and for new folks to easily breakdown or destill the excel.
Just my two cents from someone who built the former and now build the latter.
Agree that simplicity and helper columns are always better in a modeling context as long as they don't become cumbersome. Certainly very useful for reporting and perhaps not the best example for a modeling context. That said, if you go into any model I'm sure you'd be able to find a good usecase. One area that comes to mind is in monthly cash flow when there are a number of IF/OR/AND conditions that change your output. These can become nearly impossible to read very quickly and the LET formula will add some needed structure imo
Enim odit praesentium provident facere eos. Quam id quidem aut non voluptates voluptatibus. Consequatur iste nam eveniet modi quis dolores. Nostrum nam praesentium magnam voluptate. Repellat quasi voluptatem corrupti error voluptates dolor. Aut consequatur ab molestiae quisquam.
Odio et non fugit sed. Rem velit aut facilis recusandae.
Sed ullam officia esse et facilis et. Laudantium non consequuntur cum temporibus temporibus adipisci.
Ullam quo ad possimus illum sunt maiores est. Dignissimos odio non et quas facere architecto natus. Omnis aut non odio impedit dolorem. Consectetur et distinctio perferendis officia autem deserunt explicabo. In sequi ut ducimus aperiam. Sunt id voluptas quaerat sint omnis. Aut sit velit maxime natus necessitatibus.
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...