Commercial Development
Investment Analysis
Real Estate Software
planEASe

Commercial Real Estate Articles
Proforma Income Statement Terms and Methods
for Investment and Development Cash Flow Analysis

Visit planEASe
Homepage

Skip Navigation Links.

Visit planEASe
Homepage

Click the "+" nodes above to
expand the tree list.

Verify Monthly IRR and NPV Using Excel or Google Spreadsheets XIRR and XNPV article

It is important to have confidence in your analysis, and it can be important to people viewing your analysis to have numbers confirmed. This article and video shows the IRR's and NPV's in planEASe verified against the XNPV and XIRR in Microsoft Excel and Google Spreadsheets.

Start a 14 Day Free Trial

Video Title: Learn about the Verify Monthly IRR and NPV Using Excel or Google Spreadsheets XIRR and XNPV

Video Publication_Date: Sunday, May 12, 2024

Video Duration: 8:30

Video Description:
The topic for this commercial real estate investment analysis video is Verify Monthly IRR and NPV Using Excel or Google Spreadsheets XIRR and XNPV. Throughout the video planEASe Software is used to illustrate Verify Monthly IRR and NPV Using Excel or Google Spreadsheets XIRR and XNPV. The video does not use the current Proforma Example, but all the factors that the Verify Monthly IRR and NPV Using Excel or Google Spreadsheets XIRR and XNPV are sensitive to are covered.

In planEASe, all the IRR's and NPV's are computed on a monthly basis using the XIRR and XNPV process which is designed to handle irregular cash flows. To verify the planEASe IRR or NPV you need to use a spreadsheet program like Excel, Google Spreadsheets, Matlab, etc., because most financial calculators are not able to compute irregular dates and cash flows like the table shown below. The video above shows how to export these cash flows and dates to Excel and Google Spreadsheets and produce the same XIRR and XNPV.

Here are links to other descriptions of XIRR and XNPV:
IRR Verification Report
Retail - Office

These cash flows and dates are those on which the Rate of Return After Tax in the planEASe analysis of the Retail - Office are based.'

planEASe Software plans operating cash flows monthly, and assumes that cash flows occuring during the month all occur at the middle of the month.Calculators and Spreadsheets typically schedule these cash flows at the end of the year (year-end convention).Since cash flows do actually occur unevenly during the year, the mid-month convention is more accurate.

The Internal Rate of Return (IRR) is defined as the Present Value Discount Rate which makes the Net Present Value of the Cash Flows involved equal to zero. The Net Present Value of ($0.16) shown below demonstrates that the Net Present Value of these Cash Flows is very close to zero when discounted at 4.5752% and, in turn, the IRR of the Cash Flows is very close to 4.5752%.



Date

Years

Cash Flow
Present Value
Discount Factor
Present Value
at 4.5752%
1 Jan 20100.00($1,023,344.00)1.0000000($1,023,344.00)
15 Jan 20100.041,691.000.99828561,688.10
15 Feb 20100.121,680.000.99449971,670.76
15 Mar 20100.201,669.000.99109261,654.13
15 Apr 20100.281,658.000.98733411,637.00
15 May 20100.371,647.000.98371041,620.17
15 Jun 20100.451,636.000.97997981,603.25
15 Jul 20100.53(11,578.00)0.9763831(11,304.56)
15 Aug 20100.623,579.000.97268043,481.22
15 Sep 20100.703,568.000.96899163,457.36
15 Oct 20100.793,556.000.96543523,433.09
15 Nov 20100.873,545.000.96177403,409.49
15 Dec 20100.953,533.000.95824413,385.48
15 Jan 20111.043,796.000.95461013,623.70
15 Feb 20111.123,785.000.95099003,599.50
15 Mar 20111.203,773.000.94773193,575.79
15 Apr 20111.283,761.000.94413783,550.90
15 May 20111.373,749.000.94067263,526.58
15 Jun 20111.453,737.000.93710533,501.96
15 Jul 20111.533,772.000.93366593,521.79
15 Aug 20111.623,760.000.93012523,497.27
15 Sep 20111.703,748.000.92659783,472.89
15 Oct 20111.793,736.000.92319703,449.06
15 Nov 20111.872,001.000.91969601,840.31
15 Dec 20111.95779.000.9163205713.81
15 Jan 20122.04(3,586.00)0.9128455(3,273.46)
15 Feb 20122.12(108,832.00)0.9093837(98,970.05)
15 Mar 20122.203,454.000.90615723,129.87
15 Apr 20122.293,441.000.90272073,106.26
15 May 20122.373,428.000.89940763,083.17
15 Jun 20122.453,415.000.89599673,059.83
15 Jul 20122.543,548.000.89270823,167.33
15 Aug 20122.623,535.000.88932283,143.76
15 Sep 20122.713,522.000.88595023,120.32
15 Oct 20122.793,508.000.88269863,096.51
15 Nov 20122.873,495.000.87935113,073.33
15 Dec 20122.963,528.000.87612373,090.96
15 Jan 20133.043,670.000.87280123,203.18
15 Feb 20133.133,863.000.86949123,358.84
15 Mar 20133.203,850.000.86651243,336.07
15 Apr 20133.293,836.000.86322633,311.34
15 May 20133.373,822.000.86005813,287.14
15 Jun 20133.453,808.000.85679653,262.68
15 Jul 20133.54763.000.8536519651.34
15 Aug 20133.62(1,168.00)0.8504145(993.28)
15 Sep 20133.713,720.000.84718953,151.54
15 Oct 20133.793,706.000.84408013,128.16
15 Nov 20133.873,691.000.84087913,103.68
15 Dec 20133.963,725.000.83779293,120.78
15 Jan 20144.043,724.000.83461573,108.11
15 Feb 20144.133,922.000.83145063,260.95
15 Mar 20144.203,907.000.82860213,237.35
15 Apr 20144.293,892.000.82545983,212.69
15 May 20144.373,877.000.82243023,188.56
15 Jun 20144.453,862.000.81931133,164.18
15 Jul 20144.543,846.000.81630423,139.51
15 Aug 20144.623,905.000.81320853,175.58
15 Sep 20144.713,889.000.81012463,150.57
15 Oct 20144.793,874.000.80715133,126.90
15 Nov 20144.873,858.000.80409033,102.18
15 Dec 20144.961,833.000.80113911,468.49
15 Jan 20155.04(6,170.00)0.7981009(4,924.28)
15 Feb 20155.13(2,559.00)0.7950743(2,034.60)
15 Mar 20155.20(2,575.00)0.7923504(2,040.30)
15 Apr 20155.29(2,592.00)0.7893456(2,045.98)
15 May 20155.37(78,787.00)0.7864485(61,961.92)
15 Jun 20155.455,014.000.78346603,928.30
15 Jul 20155.544,998.000.78059063,901.39
15 Aug 20155.625,057.000.77763033,932.48
15 Sep 20155.715,041.000.77468133,905.17
15 Oct 20155.795,024.000.77183813,877.71
15 Nov 20155.875,007.000.76891103,849.94
15 Dec 20155.964,989.000.76608893,822.02
31 Dec 20156.001,332,012.000.76458811,018,440.49
TOTALS$311,827.00($0.16)



Written by
Michael Feakins, CCIM
of planEASe Software