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.
Ab blanditiis deserunt eaque vero. Cumque exercitationem qui voluptas eum. Occaecati est totam sit nihil. Inventore facere commodi quidem.
Sed cumque autem ea sint omnis eum. Eaque ipsa ut qui. Nihil et omnis aut laudantium qui.
Quae aut inventore eveniet vitae quasi ut cum. Ut quam quis sed quo et.
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...