This post will talk about building and maintaining financial models in Excel. While not directly related to actuarial science, this tutorial can help with building financial models if you’re interested in finance or want to understand cash flows from a financial perspective. Also, this material is covered in SOA Exams FM and MFE so this could be a useful primer as well.
Most of these descriptions are sourced from the Excel Helpfile. I’ve listed the common functions used and their accompanying description from the helpfile, occasionally adding in my own tips as well.
Contents
PV(rate, nper, pmt, [fv], [type])
Excel description: Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Rate – Required. The interest rate per period. For example, if you obtain an automobile loan at a 10 percent annual interest rate and make monthly payments, your interest rate per month is 10%/12, or 0.83%. You would enter 10%/12, or 0.83%, or 0.0083, into the formula as the rate.
Nper – Required. The total number of payment periods in an annuity. For example, if you get a four-year car loan and make monthly payments, your loan has 4*12 (or 48) periods. You would enter 48 into the formula for nper.
Pmt – Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. For example, the monthly payments on a $10,000, four-year car loan at 12 percent are $263.33. You would enter -263.33 into the formula as the pmt. If pmt is omitted, you must include the fv argument.
Fv – Optional. 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 (the future value of a loan, for example, is 0). For example, if you want to save $50,000 to pay for a special project in 18 years, then $50,000 is the future value. You could then make a conservative guess at an interest rate and determine how much you must save each month. If fv is omitted, you must include the pmt argument.
Type – Optional. The number 0 or 1 and indicates when payments are due.
NPV(rate,value1,[value2],…)
Excel description: Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Rate is discount rate, and value(s) are the cashflows (eg: use negative for outflows)
IRR(values, [guess])
Returns the internal rate of return for a series of cash flows represented by the numbers in values. These 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.
Values
– Required. An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
Values must contain at least one positive value and one negative value to calculate the internal rate of return.
IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored.Guess
– Optional. A number that you guess is close to the result of IRR.
Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can’t find a result that works after 20 tries, the #NUM! error value is returned.
In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).
If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.
RATE(nper, pmt, pv, [fv], [type], [guess])
Excel description: Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Nper – Required. The total number of payment periods in an annuity.
Pmt – Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
Pv – Required. The present value — the total amount that a series of future payments is worth now.
Fv – Optional. 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 (the future value of a loan, for example, is 0).
Type – Optional. The number 0 or 1 and indicates when payments are due.
PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
Excel description: Returns the price per $100 face value of a security that pays periodic interest.
Settlement – Required. 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 – Required. The security’s maturity date. The maturity date is the date when the security expires.
Rate – Required. The security’s annual coupon rate.
Yld – Required. The security’s annual yield.
Redemption – Required. The security’s redemption value per $100 face value.
Frequency – Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
Basis – Optional. The type of day count basis to use.
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
Excel description: Returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield.
Settlement – Required. 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 – Required. The security’s maturity date. The maturity date is the date when the security expires.
Rate – Required. The security’s annual coupon rate.
Pr – Required. The security’s price per $100 face value.
Redemption – Required. The security’s redemption value per $100 face value.
Frequency – Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
Basis – Optional. The type of day count basis to use.
DURATION(settlement, maturity, coupon, yld, frequency, [basis])
Excel description: Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price’s response to changes in yield.
Settlement – Required. 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 – Required. The security’s maturity date. The maturity date is the date when the security expires.
Coupon – Required. The security’s annual coupon rate.
Yld – Required. The security’s annual yield.
Frequency – Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
Basis – Optional. The type of day count basis to use.
