Financial Functions in Excel

So you always wanted to know the effective annual rate of interest where the interest is compounded, the accrued interest on Government security that pays annual interest or internal rate of return from a business where cash inflows and outflows are not periodic. You also wanted to know the actual rate at which you are paying interest on your car loan. You wanted answers to several such financial questions. But you did not know whom to ask. Relax . . . . Read on and please yourself . . .

1. Calculate the effective annual interest rate where interest is compounded.

Use EFFECT(nominal_rate,npery) function to calculate the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

Syntax

EFFECT (nominal_rate,npery)

Nominal_rate is the nominal interest rate.

Npery is the number of compounding periods per year. e.g., 2 if compounded semi-annually, 4 if compounded quarterly.

For example, where the nominal interest rate is 5.25% p.a. and the compounding is quarterly, the effective interest rate works out to be 5.3543%.

2. I am buying Government security that pays periodic interest. How do I calculate accrued interest on it ?

Use the AccruedInt() function to calculate accrued interest on a security that pays periodic payment.

Syntax

ACCRINT (issue,first_interest,settlement,rate,par,frequency,basis)

Issue is the security’s issue date.

First_interest is the security’s first interest date.

Settlement is the security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.

Rate is the security’s annual coupon rate.

Par is the security’s par value. If you omit par, ACCRINT uses $ 1,000.

Frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semi-annual, frequency = 2; for quarterly, frequency = 4.

Basis is the type of day count basis to use.

3. Calculate the payment for a loan (e.g., EMI) based on constant payments and constant interest rates ?

Use the PMT() function to calculate the payment for a loan (e.g., EMI) based on constant payments and constant interest rates.

Syntax

PMT (rate,nper,pv,fv,type)

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Note : you can also use PMT function to calculate payments to annuities other than bank loans. For example, you can calculate the amount required to be saved per month to accumulate a sum of amount at the end of a given period where your savings earn interest as well.

4. Calculate interest rate that I am actually paying (and the lender is earning) on my home loan or car loan ? (i.e., internal rate of return for a schedule of periodic cash flows)

Use the IRR function to calculate internal rate of return for a schedule of periodic cash flows. The cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

5. Calculate internal rate of return for a schedule of cash flows that is not necessarily periodic (e.g., in case of businesses where cash inflows and outflows are not periodic) ?

Use the XIRR() function. It returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.

Syntax

XIRR (values,dates,guess)

Values is a series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year.

6. Calculate Net Present value for a schedule of cash flows that is not necessarily periodic ?

Use the XNPV() function to calculate Net Present value for a schedule of cash flows that is not necessarily periodic.

Syntax

XNPV (rate,values,dates)

Rate is the discount rate to apply to the cash flows.

Values is a series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value.

Dates is a schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.

7. Calculate yield of a bond that pays periodic interest ?

Use the YIELD() function to calculate yield of a security that pays periodic interest.

Syntax

YIELD (settlement,maturity,rate,pr,redemption,frequency,basis)

Important dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008.

Settlement is the security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.

Maturity is the security’s maturity date. The maturity date is the date when the security expires.

Rate is the security’s annual coupon rate.

Pr is the security’s price per $100 face value.

Redemption is the security’s redemption value per $100 face value.

Frequency is the number of coupon payments per year. For annual payments, frequency=1; for semiannual, frequency = 2; for quarterly, frequency = 4.

Article by Nikunj S. Shah, Chartered Accountant