NPV, IRR & XIRR

[otis1978]

Would anybody be willing to check my NPV, IRR & XIRR results based on the following?

Discount rate = 5.00%

Cash outflow = 1,858,296 on 9/1/2021

Cash inflow = 104,994 on 10/1/2021
Cash inflow = 104,994 on 10/1/2022
Cash inflow = 104,994 on 10/1/2023
Cash inflow = 115,943 on 10/1/2024
Cash inflow = 115,943 on 10/1/2025
Cash inflow = 115,943 on 10/1/2026
Cash inflow = 115,943 on 10/1/2027
Cash inflow = 115,943 on 10/1/2028
Cash inflow = 127,043 on 10/1/2029
Cash inflow = 127,043 on 10/1/2030
Cash inflow = 127,043 on 10/1/2031
Cash inflow = 127,043 on 10/1/2032
Cash inflow = 127,043 on 10/1/2033
Cash inflow = 127,043 on 10/1/2034

Thank you

 

[joeu2004]

There is nothing to "check", since you do not provide any formulas. Probably homework; but I'll bite. (sigh)

First, technically, you cannot use NPV and IRR because the frequency of the cashflows is not consistent: all are annual except for the initial outflow.

But since the initial outflow is only one month (or one day?) before, I would combine the first and second cashflows for a close approximation.

So you might set things up as follows (assuming dates in MDY form):

Book1
	A	B	C	D	E	F
1	Rate	5.00%			XNPV	-649,581.346013907
2					XIRR	-1.62781625986099%
3	Actual Cash Flows		Annual CFs		NPV	-613,811.177637117
4	9/1/2021	-1,858,296			IRR	-1.64771120277367%
5	10/1/2021	104,994	-1,753,302
6	10/1/2022	104,994	104,994		XIRR check	9.74E-04
7	10/1/2023	104,994	104,994		IRR check	2.93E-09
8	10/1/2024	115,943	115,943
9	10/1/2025	115,943	115,943
10	10/1/2026	115,943	115,943
11	10/1/2027	115,943	115,943
12	10/1/2028	115,943	115,943
13	10/1/2029	127,043	127,043
14	10/1/2030	127,043	127,043
15	10/1/2031	127,043	127,043
16	10/1/2032	127,043	127,043
17	10/1/2033	127,043	127,043
18	10/1/2034	127,043	127,043
Sheet1

Formulas:
C5: =B5+B4
F1: =XNPV(B1,B4:B18, A4:A18)
F2: =XIRR(B4:B18, A4:A18)
F3: =NPV(B1, C5:C18)
F4: =IRR(C5:C18)
F6: =SUMPRODUCT(B4:B18 / (1+F2)^((A4:A18-A4)/365))
F7: =NPV(F4, C5:C18)

Note that we cannot use XNPV in F6 because XNPV does allow negative discount rates -- a defect, IMHO.

 

[joeu2004]

Errata, too late to edit....

Note that we cannot use XNPV in F6 because XNPV does allow negative discount rates -- a defect, IMHO.

XNPV does not allow negative discount rates.

 

[otis1978]

Thank you for the reply. Ya sorry, I did not include my formulas. I am trying to evaluate different commercial property purchase opportunities. I was using your same formula's, but wasn't netting out the 9/1/21 outflow and the 10/1/2020 inflow. Much appreciated.

Mark

 

[otis1978]

There is nothing to "check", since you do not provide any formulas. Probably homework; but I'll bite. (sigh)

First, technically, you cannot use NPV and IRR because the frequency of the cashflows is not consistent: all are annual except for the initial outflow.

But since the initial outflow is only one month (or one day?) before, I would combine the first and second cashflows for a close approximation.

So you might set things up as follows (assuming dates in MDY form):

Book1
	A	B	C	D	E	F
1	Rate	5.00%			XNPV	-649,581.346013907
2					XIRR	-1.62781625986099%
3	Actual Cash Flows		Annual CFs		NPV	-613,811.177637117
4	9/1/2021	-1,858,296			IRR	-1.64771120277367%
5	10/1/2021	104,994	-1,753,302
6	10/1/2022	104,994	104,994		XIRR check	9.74E-04
7	10/1/2023	104,994	104,994		IRR check	2.93E-09
8	10/1/2024	115,943	115,943
9	10/1/2025	115,943	115,943
10	10/1/2026	115,943	115,943
11	10/1/2027	115,943	115,943
12	10/1/2028	115,943	115,943
13	10/1/2029	127,043	127,043
14	10/1/2030	127,043	127,043
15	10/1/2031	127,043	127,043
16	10/1/2032	127,043	127,043
17	10/1/2033	127,043	127,043
18	10/1/2034	127,043	127,043
Sheet1

Formulas:
C5: =B5+B4
F1: =XNPV(B1,B4:B18, A4:A18)
F2: =XIRR(B4:B18, A4:A18)
F3: =NPV(B1, C5:C18)
F4: =IRR(C5:C18)
F6: =SUMPRODUCT(B4:B18 / (1+F2)^((A4:A18-A4)/365))
F7: =NPV(F4, C5:C18)

Note that we cannot use XNPV in F6 because XNPV does allow negative discount rates -- a defect, IMHO.

Joe, would you be willing to look at another question pertaining to my post? Does Mr. Excel allow you to upload a spreadsheet?

Thanks, Mark

 

[joeu2004]

Joe, would you be willing to look at another question pertaining to my post?

Yes.

I cannot speak for the moderators. But generally, these forums perfer one question per thread. Usually, it is better to start a new thread for "another question".

But IMHO, it is a judgment call, depending on how closely your "other question" is related to the original question and responses in this thread.

-----

Does Mr. Excel allow you to upload a spreadsheet?

Again, I cannot speak for the moderators. But generally, the preference in this forum is use the XL2BB add-in, as I did.

That said, __I__ am fine with an Excel file that is uploaded to a file-sharing website. IMHO, sometimes that it is the best way to present the data and formulas for a problem.

 

[otis1978]

Joe, if you could share with me a file-sharing website I could certainly do that, or I could upload to dropbox and make it sharable t you if that would work by any chance?

Thanks, Mark

 

[Radoslaw Poprawski]

@otis1978
you can share using onedrive or sharepoint or google drive.

 

[otis1978]

@otis1978
you can share using onedrive or sharepoint or google drive.

Yes sir, thank you for the tip

 

[joeu2004]

I could upload to dropbox and make it sharable t you

dropbox.com or box.net/files would be fine. But when you say "sharable to you", do you intend to limit access?

you can share using onedrive

I do not recommend onedrive.live.com because it might alter the content of the file, I'm told.