Data Financial Analysis

You can perform financial analysis with Excel in an easy way. Excel provides you several financial functions such as PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, and so on that enable you to quickly arrive at the financial analysis results.

In this chapter, you will learn where and how you can use these functions for your analysis.

What is Annuity?

An annuity is a series of constant cash payments made over a continuous period. For example, savings for retirement, insurance payments, home loan, mortgage, etc. In annuity functions −

A positive number represents cash received.
A negative number represents cash paid out.

Present Value of a series of Future Payments

The present value is the total amount that a series of future payments is worth now. You can calculate the present value using the Excel functions −

PV − Calculates the present value of an investment by using an interest rate and a series of future payments (negative values) and income (positive values). At least one of the cash flows must be positive and at least one must be negative.
NPV − Calculates the net present value of an investment by using a discount rate and a series of periodic future payments (negative values) and income (positive values).
XNPV − Calculates the net present value for a schedule of cash flows that is not necessarily periodic.

Note that −

PV cash flows must be constant whereas NPV cash flows can be variable.
PV cash flows can be either at the beginning or at the end of the period whereas NPV cash flows must be at the end of the period.
NPV cash flows must be periodic whereas XNPV cash flows need not be periodic.

In this section, you will understand how to work with PV. You will learn about NPV in a later section.

Example

Suppose you are buying a refrigerator. The salesperson tells you that the price of the refrigerator is 32000, but you have an option to pay out the amount in 8 years with an interest rate of 13% per annum and yearly payments of 6000. You also have an option to make the payments either at the beginning or end of each year.

You want to know which of these options is beneficial for you.

You can use Excel function PV −

PV (rate, nper, pmt, [fv ], [type])

To calculate present value with payments at the end of each year, omit type or specify 0 for type.

To calculate present value with payments at the end of each year, specify 1 for type.

You will get the following results −

Therefore,

If you make the payment now, you need to pay 32,000 of present value.
If you opt for yearly payments with payment at the end of the year, you need to pay 28, 793 of present value.
If you opt for yearly payments with payment at the end of the year, you need to pay 32,536 of present value.

You can clearly see that option 2 is beneficial for you.

What is EMI?

An Equated Monthly Installment (EMI) is defined by Investopedia as “A fixed payment amount made by a borrower to a lender at a specified date each calendar month. Equated monthly installments are used to pay off both interest and principal each month, so that over a specified number of years, the loan is paid off in full.”

EMI on a Loan

In Excel, you can calculate the EMI on a loan with the PMT function.

Suppose, you want to take a home loan of 5000000 with an annual interest rate of 11.5% and the term of the loan for 25 years. You can find your EMI as follows −

Calculate interest rate per month (Interest Rate per Annum/12)
Calculate number of monthly payments (No. of years * 12)
Use PMT function to calculate EMI

As you observe,

Present Value (PV) is the loan amount.
Future Value (FV) is 0 as at the end of the term the loan amount should be 0.
Type is 1 as the EMIs are paid at the beginning of each month.

You will get the following results −

Monthly Payment of Principal and Interest on a Loan

EMI includes both-interest and a part payment of principal. As the time increases, these two components of EMI will vary, reducing the balance.

To get

The interest part of your monthly payments, you can use the Excel IPMT function.
The payment of principal part of your monthly payments, you can use the Excel PPMT function.

For example, if you have taken a loan of 1,000,000 for a term of 8 months at the rate of 16% per annum. You can get values for the EMI, the decreasing interest amounts, the increasing payment of principal amounts and the diminishing loan balance over the 8 months. At the end of 8 months, loan balance will be 0.

Follow the procedure given below.

Step 1 − Calculate the EMI as follows.

This results in an EMI of Rs. 13261.59.

Step 2 − Next calculate the interest and principal parts of the EMI for the 8 months as shown below.

You will get the following results.

Interest and Principal paid between two Periods

You can compute the interest and principal paid between two periods, inclusive.

Compute the cumulative interest paid between 2^nd and 3^rd months using the CUMIPMT function.
Verify the result summing up the interest values for 2^nd and 3^rd months.
Compute the cumulative principal paid between 2^nd and 3^rd months using the CUMPRINC function.
Verify the result summing up the principal values for 2^nd and 3^rd months.