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.
Voluptatibus reprehenderit magni perspiciatis ratione reiciendis est quisquam. Dolor et accusantium quasi ullam.
Voluptates iure libero et dolores. Fuga aut sunt nemo culpa autem laudantium inventore. Rerum earum et dolores itaque. Facere incidunt placeat suscipit non molestiae sequi eveniet. Necessitatibus magnam excepturi totam enim minus enim porro aut.
Deleniti fugiat explicabo quam tempora ab. Et error pariatur maxime voluptatum neque. Qui laboriosam labore odio molestias nostrum.
Sit inventore optio et illum laborum. Commodi non sit et aut laudantium cum quos.
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...