I am trying to calculate the NPV of a loan using monthly payments, but when using XNPV I get some wacky results.
Loan Amount: $11M I/R: 3% (amortizing) Term: 480 months (40 years) Monthly Pmt=39378.29 Loan Proceeds received: 12/31/19 M1 of Payment= 1/31/20
Using the discount rate of the loan (3%): should yield me an NPV equal to the loan, but when I use the XNPV function, my npv is off by $6-7M. If I use the vanilla NPV function, it works. The present value of the payments equals the loan amount.
Why does the XNPV give me such a drastically different answer? Any help would be appreciated, been stumped by a while and really never use XNPV. In theory, they should be the same if the period interval is the same.
Thanks
did you use a monthly interest rate(3%/12) or 3%
Appreciate the response man. I tried both, and even the effective compounded rate.
Hmmmm...The XIRR for the cash flows is 3.03396488%, which would effectively be the discount rate. And the NPV of 6.985million and your payments is 11 million your notional loan value.
What NPV is saying is that you need ~7 million of dolars today "plus" cash flow to get $11 million dollars of value from the cash flows at the 3% reinvestment rate.
I have to think this through more.
Vel sit nesciunt aspernatur quidem velit aut. Debitis libero rerum consequuntur a exercitationem rem dolores ipsum. Corporis molestiae est officia reiciendis aperiam nemo facere. Quasi unde in quia ex omnis dolor blanditiis.
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...