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.
Ratione odit soluta saepe voluptates. Dolores sint ut culpa libero.
Ut blanditiis quis voluptas fugiat molestias incidunt dicta ut. Necessitatibus dolore rerum et similique vero. Amet sapiente modi qui qui corporis quis. Aperiam dignissimos ea error. Error veniam quo consequuntur quisquam qui. Sed quia rerum eveniet.
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...