পৃষ্ঠাসমূহ

.

Search Your Article

Total Pageviews

Tuesday, November 5, 2024

IRR function in Excel

 

IRR function in Excel

 

Use the IRR function in Excel to calculate a project's internal rate of return. The internal rate of return is the discount rate that makes the net present value equal to zero.

Simple IRR example

For example, project A requires an initial investment of $100 (cell B5).

1. We expect a profit of $0 at the end of the first period, a profit of $0 at the end of the second period and a profit of $152.09 at the end of the third period.

Cash Flows

Note: the discount rate equals 10%. This is the rate of return of the best alternative investment. For example, you could also put your money in a savings account at an interest rate of 10%.

2. The correct NPV formula in Excel uses the NPV function to calculate the present value of a series of future cash flows and subtracts the initial investment.

Net Present Value

Explanation: a positive net present value indicates that the project’s rate of return exceeds the discount rate. In other words, it's better to invest your money in project A than to put your money in a savings account at an interest rate of 10%.

3. The IRR function below calculates the internal rate of return of project A.

IRR function in Excel

4. The internal rate of return is the discount rate that makes the net present value equal to zero. To clearly see this, replace the discount rate of 10% in cell B2 with 15%.

Net Present Value of 0

Explanation: a net present value of 0 indicates that the project generates a rate of return equal to the discount rate. In other words, both options, investing your money in project A or putting your money in a high-yield savings account at an interest rate of 15%, yield an equal return.

5. We can check this. Assume you put $100 into a bank. How much will your investment be worth after 3 years at an annual interest rate of 15%? The answer is $152.09.

Compound Interest

Conclusion: you can compare the performance of a project to a savings account with an interest rate equal to the IRR.

Present Values

For example, project B requires an initial investment of $100 (cell B5). We expect a profit of $25 at the end of the first period, a profit of $50 at the end of the second period and a profit of $152.09 at the end of the third period.

1. The IRR function below calculates the internal rate of return of project B.

Internal Rate of Return

2. Again, the internal rate of return is the discount rate that makes the net present value equal to zero. To clearly see this, replace the discount rate of 15% in cell B2 with 39%.

NPV equals 0

Explanation: a net present value of 0 indicates that the project generates a rate of return equal to the discount rate. In other words, both options, investing your money in project B or putting your money in a high-yield savings account at an interest rate of 39%, yield an equal return.

3. We can check this. First, we calculate the present value (pv) of each cash flow. Next, we sum these values.

Sum Present Values

Explanation: instead of investing $100 in project B, you could also put $17.95 in a savings account for 1 year, $25.77 in a savings account for 2 years and $56.28 in a savings account for three years, at an annual interest rate equal to the IRR (39%).

IRR rule

The IRR rule states that if the IRR is greater than the required rate of return, you should accept the project. IRR values are frequently used to compare investments.

1. The IRR function below calculates the internal rate of return of project X.

IRR

Conclusion: if the required rate of return equals 15%, you should accept this project because the IRR of this project equals 29%.

2. The IRR function below calculates the internal rate of return of project Y.

Higher IRR

Conclusion: in general, a higher IRR indicates a better investment. Therefore, project Y is a better investment than project X.

3. The IRR function below calculates the internal rate of return of project Z.

Low Cash Flows

Conclusion: a higher IRR isn't always better. Project Z has a higher IRR than project Y but the cash flows are much lower.

Monday, November 4, 2024

NPV formula in Excel

 

NPV formula in Excel

 

The correct NPV formula in Excel uses the NPV function to calculate the present value of a series of future cash flows and subtracts the initial investment.

Net Present Value

For example, project X requires an initial investment of $100 (cell B5).

1. We expect a profit of $0 at the end of the first period, a profit of $50 at the end of the second period and a profit of $150 at the end of the third period.

Cash Flows

2. The discount rate equals 15%.

Discount Rate

Explanation: this is the rate of return of the best alternative investment. For example, you could also put your money in a high-yield savings account at an interest rate of 15%.

3. The NPV formula below calculates the net present value of project X.

Net Present Value Formula

Explanation: a positive net present value indicates that the project’s rate of return exceeds the discount rate. In other words, it's better to invest your money in project X than to put your money in a high-yield savings account at an interest rate of 15%.

4. The NPV formula below calculates the net present value of project Y.

Project Y

Explanation: the net present value of project Y is higher than the net present value of project X. Therefore, project Y is a better investment.

Understanding the NPV function

The NPV function simply calculates the present value of a series of future cash flows. This is not rocket science.

1. For example, project A requires an initial investment of $100 (cell B5). We expect a profit of $0 at the end of the first period, a profit of $0 at the end of the second period and a profit of $152.09 at the end of the third period.

Net Present Value of 0

Explanation: a net present value of 0 indicates that the project generates a rate of return equal to the discount rate. In other words, both options, investing your money in project A or putting your money in a high-yield savings account at an interest rate of 15%, yield an equal return.

2. We can check this. Assume you put $100 into a bank. How much will your investment be worth after 3 years at an annual interest rate of 15%? The answer is $152.09.

Compound Interest

Note: the internal rate of return of project A equals 15%. The internal rate of return is the discount rate that makes the net present value equal to zero. Visit our page about the IRR function to learn more about this topic.

3. The NPV function simply calculates the present value of a series of future cash flows.

NPV function in Excel

4. We can check this. First, we calculate the present value (pv) of each cash flow. Next, we sum these values.

Sum Present Values

Explanation: $152.09 in 3 years is worth $100 right now. $50 in 2 years is worth 37.81 right now. $25 in 1 year is worth $21.74 right now. Would you trade $159.55 for $100 right now? Of course, so project B is a good investment.

5. The NPV formula below calculates the net present value of project B.

Net Present Value

Explanation: project B is a good investment because the net present value ($159.55 - $100) is greater than 0.

Sunday, November 3, 2024

Loan Amortization Schedule in Excel

 

Loan Amortization Schedule in Excel

 

This example teaches you how to create a loan amortization schedule in Excel.

1. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. We use named ranges for the input cells.

PMT function

2. Use the PPMT function to calculate the principal part of the payment. The second argument specifies the payment number.

Principal Part

3. Use the IPMT function to calculate the interest part of the payment. The second argument specifies the payment number.

Interest Part

4. Update the balance.

Update Balance

5. Select the range A7:E7 (first payment) and drag it down one row. Change the balance formula.

Different Balance Formula

6. Select the range A8:E8 (second payment) and drag it down to row 30.

Loan Amortization Schedule

It takes 24 months to pay off this loan. See how the principal part increases and the interest part decreases with each payment.

Saturday, November 2, 2024

CAGR formula in Excel

 

CAGR formula in Excel

 

There's no CAGR function in Excel. However, simply use the RRI function in Excel to calculate the compound annual growth rate (CAGR) of an investment over a period of years.

1. The RRI function below calculates the CAGR of an investment. The answer is 8%.

Compound Annual Growth Rate

Note: the RRI function has three arguments (number of years = 5, start = 100, end = 147).

2. The CAGR measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis. We can check this.

Compounded Annually

which is the same as:

Steady Rate

Note: again, number of years or n = 5, start = 100, end = 147, CAGR = 8%.

3. Knowing this, we can easily create a CAGR formula that calculates the compound annual growth rate of an investment in Excel.

A2 = A1 * (1 + CAGR)n

end = start * (1 + CAGR)n

end/start = (1 + CAGR)n

(end/start)1/n = (1 + CAGR)

CAGR = (end/start)1/n - 1

4. The CAGR formula below does the trick.

CAGR formula in Excel

Note: in other words, to calculate the CAGR of an investment in Excel, divide the value of the investment at the end by the value of the investment at the start. Next, raise this result to the power of 1 divided by the number of years. Finally, subtract 1 from this result.

Friday, November 1, 2024

Compound Interest Formula in Excel

 

Compound Interest Formula in Excel

 What's compound interest and what's the formula for compound interest in Excel? This example gives you the answers to these questions.

1. Assume you put $100 into a bank. How much will your investment be worth after 1 year at an annual interest rate of 8%? The answer is $108.

Compound Interest Example

2. Now this interest ($8) will also earn interest (compound interest) next year. How much will your investment be worth after 2 years at an annual interest rate of 8%? The answer is $116.64.

Investment After 2 Years

3. How much will your investment be worth after 5 years? Simply drag the formula down to cell A6.

Investment After 5 Years

The answer is $146.93.

4. All we did was multiplying 100 by 1.08, 5 times. So we can also directly calculate the value of the investment after 5 years.

Compound Interest in Excel

which is the same as:

Compound Interest Formula

Note: there is no special function for compound interest in Excel. However, you can easily create a compound interest calculator to compare different rates and different durations.

5. Assume you put $100 into a bank. How much will your investment be worth after 5 years at an annual interest rate of 8%? You already know the answer.

Compounded Interest Calculator

Note: the compound interest formula reduces to =100*(1+0.08/1)^(1*5), =100*(1.08)^5

6. Assume you put $10,000 into a bank. How much will your investment be worth after 15 years at an annual interest rate of 4% compounded quarterly? The answer is $18,167.

Compounded Quarterly

Note: the compound interest formula reduces to =10000*(1+0.04/4)^(4*15), =10000*(1.01)^60

7. Assume you put $10,000 into a bank. How much will your investment be worth after 10 years at an annual interest rate of 5% compounded monthly? The answer is $16,470.

Compounded Monthly

Note: the compound interest formula always works. If you're interested, download the Excel file and try it yourself!

Thursday, October 31, 2024

Investment or Annuity in Excel

 

Investment or Annuity in Excel

 This example teaches you how to calculate the future value of an investment or the present value of an annuity.

Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)?

Investment

Assume that at the end of every year, you deposit $100 into a savings account. At an annual interest rate of 8%, how much will your investment be worth after 10 years?

1. Insert the FV (Future Value) function.

Insert FV function

2. Enter the arguments.

Future Value of the Investment

In 10 years time, you pay 10 * $100 (negative) = $1000, and you'll receive $1,448.66 (positive) after 10 years. The higher the interest, the faster your money grows.

Note: the last two arguments are optional. If omitted, Pv = 0 (no present value). If Type is omitted, it is assumed that payments are due at the end of the period.

Annuity

Assume you want to purchase an annuity that will pay $600 a month, for the next 20 years. At an annual interest rate of 6%, how much does the annuity cost?

1. Insert the PV (Present Value) function.

Insert PV function

2. Enter the arguments.

Present Value of the Annuity

You need a one-time payment of $83,748.46 (negative) to pay this annuity. You'll receive 240 * $600 (positive) = $144,000 in the future. This is another example that money grows over time.

Note: we receive monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper. The last two arguments are optional. If omitted, Fv = 0 (no future value). If Type is omitted, it is assumed that payments are due at the end of the period. This annuity does not take into account life expectancy, inflation etc.

Wednesday, October 30, 2024

Loans with Different Durations in Excel

 Loans with Different Durations in Excel

 

This example teaches you how to compare loans with different durations in Excel.

1. First, we calculate the monthly payment on a loan with an annual interest rate of 6%, a 20-year duration and a present value (amount borrowed) of $150,000.

Monthly Payment

Note: we make monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper (total number of periods).

2. Next, select the range A2:D2 and drag it down two rows.

3. Change the duration of the other two loans to 25 and 30 years.

Result:

Loans with Different Durations Example

The monthly payment over 30 years ($899.33) looks good in contrast to the $966.45 and $1,074.65. Right?

4. But now we calculate the Total Paid for each loan.

Loans with Different Durations in Excel

The monthly payment over 30 years ($899.33) suddenly does not look so attractive anymore. Conclusion: the longer the duration of the loan, the more interest you pay.

Tuesday, October 29, 2024

PMT function in Excel

 

PMT function in Excel

 

The PMT function in Excel calculates the payment for a loan based on constant payments and a constant interest rate. This page contains many easy to follow PMT examples.

PMT examples

Consider a loan with an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that's what you hope to achieve when you pay off a loan).

1. The PMT function below calculates the annual payment.

Annual Payment

Note: if the fifth argument is omitted, it is assumed that payments are due at the end of the period. We pay off a loan of $150,000 (positive, we received that amount) and we make annual payments of $13,077.68 (negative, we pay).

2. The PMT function below calculates the quarterly payment.

Quarterly Payment

Note: we make quarterly payments, so we use 6%/4 = 1.5% for Rate and 20*4 = 80 for Nper (total number of periods).

3. The PMT function below calculates the monthly payment.

Monthly Payment

Note: we make monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper (total number of periods).

Consider an investment with an annual interest rate of 8% and a present value of 0. How much money should you deposit at the end of each year to have $1,448.66 in the account in 10 years?

4. The PMT function below calculates the annual deposit.

Annual Deposit

Explanation: in 10 years time, you pay 10 * $100 (negative) = $1000, and you'll receive $1,448.66 (positive) after 10 years. The higher the interest, the faster your money grows.

Consider an annuity with an annual interest rate of 6% and a present value of $83,748.46 (purchase value). How much money can you withdraw at the end of each month for the next 20 years?

5. The PMT function below calculates the monthly withdrawal.

Monthly Withdrawal

Explanation: you need a one-time payment of $83,748.46 (negative) to pay this annuity. You'll receive 240 * $600 (positive) = $144,000 in the future. This is another example that money grows over time.

PPMT and IPMT

Consider a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000.

1. The PMT function below calculates the monthly payment.

PMT function in Excel

Note: we make monthly payments, so we use 5%/12 for Rate and 2*12 for Nper (total number of periods).

2. The PPMT function in Excel calculates the principal part of the payment. The second argument specifies the payment number.

PPMT function

Explanation: the PPMT function above calculates the principal part of the 5th payment.

3. The IPMT function in Excel calculates the interest part of the payment. The second argument specifies the payment number.

IPMT function

Explanation: the IPMT function above calculates the interest part of the 5th payment.

4. It takes 24 months to pay off this loan. Create a loan amortization schedule (see picture below) to clearly see how the principal part increases and the interest part decreases with each payment.

Loan Amortization Schedule

Note: the principal part and the interest part always add up to the payment amount.