aurino.com

aurino ra djamaris

aurino.com header image 2

Menghitung NPV, IRR, xNPV dan xIRR dng Excel

March 1st, 2008 · 1 Comment

Ms Excel File: Calculate NPV, IRR, xNPV, and xIRR

Menghitung NPV, IRR, xNPV dan xIRR dng Ms Excel

  1. NPV (Net Present Value)
    NPV adalah selisih antara present value dari investasi dengan nilai sekarang dari penerimaan-penerimaan kas bersih di masa yang akan datang. Untuk menghitung nilai sekarang perlu ditentukan tingkat bunga yang relevan.
  2. IRR (Internal Rate of Return)
    Metode IRR ini digunakan untuk mencari tingkat bunga yang menyamakan nilai sekarang
    dari arus kas yang diharapkan di masa datang, atau penerimaan kas, dengan mengeluarkan investasi awal. Caranya, dengan menghitung nilai sekarang dari arus kas
    suatu investasi dengan menggunakan suku bunga yang wajar, misalnya 10 %. kemudian
    di bandingkan dengan biaya investasi, jika nilai investasi lebih kecil, maka di coba lagi
    dengan penghitungan suku bunga yang lebih tinggi demikian seterusnya sampai biaya
    investasi menjadi sama besar. Apabila dengan suku bunga wajar tadi nilai investasi lebih
    besar, maka harus di coba lagi dengan suku bunga yang lebih rendah sampai
    mendapatkan nilai investasi yang sama besar dengan nilai sekarang.

Net Present Value

The net present value of an income stream is the sum of the present values of the individual amounts in the income stream.  Each future income amount in the stream is discounted, meaning that it is divided by a number representing the opportunity cost of holding capital from now (year 0) until the year when income is received or the outgo is spent. The opportunity cost can either be how much you would have earned investing the money someplace else, or how much interest you would have had to pay if you borrowed money.

The word “net” in “net present value” indicates that our calculation
includes the initial costs as well as the subsequent profits. It also reminds
us that all the amounts in the income stream are net profits, revenues
minus cost. In other words, “net” means the same as “total” here.

The net present value of an investment tells you how this investment compares either with your alternative investment or with borrowing, whichever applies to you.  A positive net present value means this investment is better.  A negative net present value means your alternative investment, or not borrowing, is better.Consider again this income stream:

Year 0 1 2 3 4 5 6
Income amounts -$1000 $200 $200 $200 $200 $200 $200

Let’s assume that the discount rate (the interest rate that you could earn elsewhere or at which you could borrow) will not change over the life of the project. This makes the calculation simpler. With this assumption, we can use the usual formula:

Present Value of any one income amount = (Income amount) / ( (1 + Discount Rate) to the a power)

a is the number of years into the future that the income amount will be received (or spent, if the income amount is negative).

The net present value (NPV) of a whole income stream is the sum of these present values of the individual amounts in the income stream. If we still assume that income comes or goes in annual bursts and that the discount rate will be constant in the future, then the NPV has this formula:

Varying future interest rates

The future interest rate does not have to be constant for this theory to apply. The interest rate can vary, but that makes the formulas messier. For example, if r1 is the expected interest rate next year, and r2 is the expected interest rate the year after that, then the present value today of I2 income in year 2 is
I2/(1+r1)(1+r2).

The I ‘s are income amounts for each year.  The subscripts (which are also the exponents in the denominators) are the year numbers, starting with 0, which is this year.  The discount rate — assumed to be constant in the future — is r. The number of years the investment lasts is n.Three properties of the net present value of an income stream are:

1. Higher income amounts make the net present value higher.  Lower income amounts make the net present value lower.

Enable Java in your browser to see this applet.
The NPV box on the right shows the net present value, which is the total of the amounts in the boxes in the Discounted row.  (The total may be slightly off, due to rounding.)

2. If profits come sooner, the net present value is higher.  If profits come later, the net present value is lower.

Try it yourself. This applet lets you move the income amounts to later or earlier. You can see how that changes the net present value of the income stream.

3. Changing the discount rate changes the net present value.  For an investment with the common pattern of having costs early and profits later, a higher discount rate makes the net present value smaller.


To summarize what was just illustrated, the net present value is higher if the income amounts are larger, or if they come sooner, or if the discount rate is lower.  The net present value is lower if the income amounts are smaller, or if they come later, or if the discount rate is higher.

Internal Rate of Return

In the example we’ve been using, if you keep the income amounts at their original -1000, 200, 200, 200, 200, 200, and 200, and set the discount rate to 0.0547, the net present value becomes 0.  This discount rate, 0.0547 or 5.47%, is the internal rate of return for this investment — it is the discount rate that makes the net present value equal 0. You can try this below, by setting the discount rate to 0.0547.

If you now raise any of the income amounts in years 1 through 6 (feel free to edit an income amount and see for yourself), you will need a higher discount rate to bring the net present value back to 0.  That would seem to imply that projects with higher incomes have higher internal rates of return.Similarly, if you lower any of the income amounts in years 1 through 6, then a lower discount rate will be needed to bring the net present value back up to 0.  That would seem to imply that projects with lower incomes have lower internal rates of return.

These seeming implications are actually often true, if the projects being compared have about the same shape, with the costs coming early and the benefits coming late, and if the projects being compared switch from net outgo to net income at about the same time. Otherwise, though, the implications might not be true.

Tags: Economics · Financial · Investment · management · Ms excel · Quantitative

1 response so far ↓

  • 1 sebelas // Jun 12, 2012 at 11:53 am

    Thx bro… sangat membantu
    untuk IR-nya

You must log in to post a comment.