This is a user generated content for MyStory, a YourStory initiative to enable its community to contribute and have their voices heard. The views and writings here reflect that of the author and not of YourStory.

Feeling lost about your investment gains? Let’s talk about NPV, IRR and more…

Ratnakar Pandey 4 Stories

Saturday March 25, 2017 , 4 min Read

You might be a personal investor trying to evaluate gains from your equity, debt, real estate or any other investments. Or you could be an analyst working for a corporate trying to assess financial gains from a project or a business idea. Knowing how to compute the financial gains from your investments is imperative for you to make accurate decisions.

This article will talk about the underpinning and computation of key financial metrics such as NPV and IRR and how to compute these metrics in Microsoft Excel. Since we will be using a lender who issues a loan to others as an example in this article, we will touch upon the concepts such as EMI and APR as well. Without any further ado, let’s jump into it.

First, let’s talk about the XYZ Bank who we are using as a prop throughout this article. The XYZ bank borrows money from private institutional investors at a certain interest rate (cost of borrowing) and in turn uses this money to lend to retail customers at a significantly higher interest rate. The difference economics benefits for the XYZ bank. We will look at the following loan the XYZ bank has issued to a customer-

• Loan Amount = $ 200,000

• Loan Duration (Tenure of Loan) = 12 Months

• Cost of Borrowing (the rate at which the bank borrowed this money) = 5%

• Interest Rate (the rate at which the bank lends this money to a customer) = 15%

• Loan Origination Fee = $ 1000

Next we will introduce you to some key terms related to this loan from both customer and bank’s point of view.

Cashflow- Tracking of actual cash movement between the bank and customer. This is the foundation stone of all further financial computations. In our toy case study, the total cash amount paid by the customer on the loan is $ 216,620. This includes the interest rate and principal amount.

Discounted Cashflow- Cashflow in future discounted to the present value using a discount rate. Please see NPV section for more details. The discounted cashflow in our case comes out to be $210,988. The difference in the amount in the cashflow ($ 216,620) and the discounted cashflow ($210,988) is driven by discounting.

Equate Monthly Installment (EMI)- An equal monthly amount that the customer pays to the bank towards repayment of the principal and interest rate. In Excel, “PMT” formula can be used for computing EMI. During the early stages of the loan duration, monthly repayment amount will include more interest amount and less repayment amount. As the loan matures, EMI will have bigger component of the principal amount.

Net Present Value (NPV)- NPV uses Time Value of Money (discounting future cashflow to present value) and used as a measure of taking up a project. As you may have heard, “A bird in the hand is worth two in the bush”, the main concept of the discounting future cash flow is precisely that. In other words, the money that we receive in the future is not worth the same as it is worth now due to various factors such as inflation etc.

A good rule of thumb related to NPV and any investment is that a good investment should have NPV value significantly greater than zero. Moreover, higher the NPV, better is the investments. This can be used as a yardstick to measure different investments. In Excel “XNPV” formula can be used for computing the NPV.

Internal Rate of Return (IRR)- Like NPV, IRR is also a metric for measuring the return of an investment. Mathematically speaking, IRR is the discount rate at which the NPV will be zero. As with NPV, higher value of IRR is preferred for an attractive investment. At a bare minimum, the IRR should be higher than the Cost of Capital (COF) for making a worthwhile investment. In Excel, IRR can be computed by using the formula “XIRR”.

Annual Percentage Rate (APR)- The difference between the APR and interest rate is that APR includes miscellaneous fees and charges related to a loan, in addition to the interest rate of the loan. Calculation of APR in Excel is not that straightforward. It involves following steps-

1. Computing EMI including all fees along with the loan principal amount.

2. Back compute the rate using the EMI computation from the above step.

Typically, APR on a loan will be always higher or equal to interest rate of the loan.

In our example, APR of the loan comes out be 16%, when the interest rate is 15% on the loan. The difference between the APR and interest rate is driven by $ 1000 onetime loan processing fee.