Few innovations have had more of an impact on investment analysis than spreadsheet software. Dominated by Microsoft Excel for the last 25 years, this sector is currently used by an estimated 78% of U.S. businesses. While most know how to quickly copy, sum, drag and format, fewer may know about the real power of this software – a whole host of integrated functions and formulas.
I ran into a long-time reader a few weeks ago, who remarked on covering some ‘soft content’ during recent columns. As a result, this month we are going back to school to discuss some useful but perhaps little-known functions to help get your Excel game in gear.
Present value is a foundational concept and represents a value today for a series of cash flows to be received in the future at a specific discount rate. This concept has tremendous value to a real estate investor, as it allows us to determine what this series of cash flows is worth today. The formula is =PV(discount rate, time periods, periodic payments, future value).
Future value is also useful and is the exact opposite of present value. It rep- resents that value at some point in the future of a present lump sum value and/or a series of periodic payments, collectively compounded at a given compounding rate to a specific point in the future. The formula is =FV(compounding rate, time periods, periodic payment, present value).
We have discussed the concept of net present value several times over the years in these very pages. A kissing cousin to IRR, this concept adds a slight twist by discounting all future cash flows back at a target discount rate and nets the sum against the initial investment. The initial investment can be entered as zero, which makes this formula a very common way to determine the current value of an investment at a given discount rate. The formula is =NPV(target discount rate, series of periodic cash flows).
The addition of the ‘x’ allows for more specific control over the timing of cash flows. While NPV considers annual periodic cash flows, XNPV can distinguish between monthly, quarterly, semi-annual or annual periods, all within the same range of cash flows. The formula is =XNPV(target discount rate, series of periodic cash flows, range of associated dates).
Enter NPV’s kissing cousin. Most of us think of IRR as the rate of return that each dollar earns in an investment while it’s invested. But there is an alternative definition – IRR determines the exact rate at which all future cash flows dis- counted back to the present and netted against the initial investment equals zero. As a result, the IRR of an investment will be the same as NPV’s target discount rate when NPV equals zero. IRR is a very important metric to many investors but thankfully, the formula is simple: =IRR(series of periodic cash flows).
Similar to NPV, the addition of the ‘x’ allows for more control over timing. While IRR is an annual measure, XIRR can accurately calculate a mixture of time periods, including monthly, quarterly, semi-annually or annually. The formula is =XIRR(series of periodic cash flows, range of associated dates)
Back in the day, any real estate professional worth their salt would have a little red covered book called the “Ellwood Tables for Real Estate Appraising and Financing” right by their side. Filled with page after page of tables, it allowed the reader to quickly figure out the annual loan payment at a variety of nominal interest rates and amortization periods. This function in Excel makes the process a snap: =PMT(nominal interest rate, amortization period, initial loan amount). One word of caution – most loans are amortized and paid on a monthly basis so be sure that the nominal interest rate and amortization period both reflect this.
FV to find loan balance
Once you determine the loan payment, you can easily find the loan balance at any point during the life of that loan. The only time the future value (FV) will be zero is once the final payment is made and the loan is fully amortized. The formula is =FV(nominal interest rate, specific period for loan balance, periodic loan payment, original loan amount). Two input items of note. First, be sure that the interest rate, specific period and periodic loan payment all reflect months if using monthly compounding. And second, be sure to enter the periodic payment as a negative if the original loan amount is entered in as a positive.
Sticking with the loan theme, we know that the concept of classic loan amortization results in a portion of each periodic payment representing interest and a portion representing principal, which in turn reduces the outstanding loan balance. While both portions are important, we can use the IPMT function to determine exactly how much interest is associated with a particular periodic payment. The formula is =IPMT(nominal interest rate, specific payment period for interest component, total amortization period, original loan amount). Once again, be sure that all of the components represent months if using monthly compounding.
While IRR is a very useful tool, it has limitations related to its treatment of both negative and positive cash flows that the primary investment produce. Diving into the nuances associated with the treatment of negative cash flows can be the subject of an entire column (spoiler alert) so for now, just set that one aside. As for positive cash flows, IRR makes no assumption for cash flows that come out of an investment. The only thing that IRR cares about money coming out of a deal is that it is no longer in that deal. That’s an issue because, as an investor, I can re-invest that money into another investment. A concept known as Modified Internal Rate of Return (or MIRR) addresses this limitation by introducing consideration of a secondary investment, and associated reinvestment rate, for any positive cash flows that are generated by the primary investment. The formula is =MIRR(series of cash flows, safe rate applied to negative cash flows, reinvestment rate applied to positive cash flows).
While Excel has certainly changed the landscape of accounting and financial analysis, it wasn’t the pioneer. If you have a long enough memory, you may be thinking about Lotus 1-2-3, which was introduced in 1983. But four years prior to that was the OG – VisiCalc was the first spreadsheet software developed for personal computers in 1979. It is rumored that when co-founder Dan Bricklin, then a student at Harvard, showed his creation to a group of accountants, they sat in stunned belief at the ease by which simply changing a number would automatically update the sum total. And then, one of them started to cry. We certainly have come a long way, baby.
Financial Strategies by Alec J. Pacella, for October 2022 Properties Magazine.