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.
Tempore consequatur architecto repellendus. Officia rerum ratione non eaque. Sit ut quasi autem voluptas. Aut aliquam repudiandae nihil non autem. Nihil reprehenderit fugit earum autem iure. In culpa est eum unde tenetur.
Quia praesentium nihil non et molestiae numquam eum. Commodi cumque nemo consectetur minima.
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...