Monthly vs. Yearly IRR or NPV (Discounted Cash Flow Measure Comparison) article
Does calculating an IRR monthly vs yearly make a difference? What about the question of whether to use the beginning or end of period? This article and video use the XIRR (and XNPV) process introduced by Microsoft Excel in 1997 to show why monthly calculation is important, and using the XIRR (and XNPV) means the question of beginning or end of period does not matter.
Start a 14 Day Free Trial
|
|
Video Title: Learn about the Monthly vs. Yearly IRR or NPV (Discounted Cash Flow Measure Comparison)
Video Publication_Date: Tuesday, May 21, 2024
Video Duration: 6:30
Video Description:
The topic for this commercial real estate investment analysis video is Monthly vs. Yearly IRR or NPV (Discounted Cash Flow Measure Comparison). Throughout the video planEASe Software is used to illustrate Monthly vs. Yearly IRR or NPV (Discounted Cash Flow Measure Comparison). The video does not use the current Proforma Example, but all the factors that the Monthly vs. Yearly IRR or NPV (Discounted Cash Flow Measure Comparison) are sensitive to are covered.
|
How is a monthly IRR or NPV different from a Yearly IRR or NPV?
A Yearly IRR assumes that there is one total Cash Flow amount each year. If you are using an End-of-Period Convention, that Cash Flow is assumed to occur at the end of the year. Using this method, for example, a 30 year 12% mortgage evaluates to a 12% IRR for the lender. Most bankers know this is false, and they will consider the fact that the payments are received each month rather than at the end of the year, and correctly calculate that their IRR (Lender's Yield) on the mortgage is 12.68%. It is easy to correct for this calculation deficiency (since the monthly payment is always the same) by using a "Mid-Year Convention" which assumes that the annual cash flow occurs at the mid-point of the year involved. Using this convention, the computed Yearly IRR for the 12% mortgage returns to the (correct) 12.68%.
For the Mid-Year Convention to be accurate in computing IRR and NPV, however, the monthly cash flows must all be equal. This is almost always false with real estate investments, and in some cases it is grossly false. Consider development or other capital spending items such as rehabilitation. Think of the cash flow affect of re-financing or monthly draws on a development loan. Consider that leases end --- and their renewals (or new tenants) may involve Vacant Periods, Free Rent, Tenant Improvements and Commissions, all of which do not occur in convenient annual time frames. These and other events combine to assure that the monthly cash flows for real estate investments are and will be unequal. In turn, Yearly IRR and NPV calculations are almost always inaccurate, and sometimes grossly inaccurate.
For these reasons, competent real estate analysis software must compute monthly cash flows and compute the IRR and NPV measures on a monthly basis if those measures are to be accurate. Since these measures are continuously used in practice to compare and decide between real estate (and other) investments, accuracy is mandatory if proper decisions are to be made. In recognition of this, Excel (for example) corrected it's previous (until 1996) Yearly IRR and NPV routines with new XIRR and NPV routines that expressly consider the date of a cash flow rather than assuming they occur a year apart. Other spreadsheet programs such as Google and MATLAB also now offer this capability.
The video above shows the difference between a Yearly IRR versus a Monthly IRR using the XIRR process. The XIRR process that is able to handle irregular monthly cash flows is the process used by planEASe.
XIRR Verification
If you are interested how to verify the monthly NPV look to this page Verify Monthly IRR and NPV Using Excel or Google Spreadsheets XIRR and XNPV.
Written by
Michael Feakins, CCIM
of planEASe Software