Microsoft® Excel Spreadsheet Exercise #3

How to use this section: Each exercise requires the development of an Excel spreadsheet. The exercises are keyed to sections in the text Engineering Economy, 5th edition, by Blank and Tarquin. Appendix A of the text is a complete primer for using Excel and the financial functions pertinent to engineering economic analysis. Sample problems are included in this appendix for setting up each function.

The spreadsheet exercises presented here are especially well suited to an engineering economy course with laboratory sessions or activities that help a student become more adept with spreadsheet-based solutions.

Spreadsheet Exercise #3

Introduction to Engineering Economy Formulas

Exercise Objective: This exercise introduces you to basic engineering economy formulas and financial functions that are included with Excel. Refer to Appendix A of the text for additional detail on some of these functions. You will become familiar with the following functions:

• NOMINAL(effect_rate, npery) - computes nominal rate
• EFFECT(nominal_rate, npery) - computes effective rate
• RATE(nper,pmt,pv,fv,type,guess) - computes interest rate of an annuity (periodic amounts are the 'pmt' values)
• PV(rate,nper,pmt,fv,type) - computes present worth value of periodic amounts; does not include any cash flow in year 0
• FV(rate,nper,pmt,pv,type) - computes the future worth value of an periodic payments
• NPV(rate,value1,value2, ...) - computes the PW of some non-uniform stream of cash flows; does not include cash flow in year 0
• NPER(rate, pmt, pv, fv, type) - computes number of payment periods for a stated PV to equal a stated FV
• PMT(rate,nper,pv,fv,type) - computes periodic payment for an annuity
• IPMT(rate,per,nper,pv,fv,type) - computes interest portion of a specific payment for some period of time
• PPMT(rate,per,nper,pv,fv,type) - computes principal portion of a payment for some period
• CUMIPMT(rate,nper,pv,start_period,end_period,type) - computes cumulative interest paid on a loan over some interval
• CUMPRINCE(rate,nper,pv,start_period,end_period,type) - computes cumulative principal paid on a loan over some interval

Blank and Tarquin Text Reference: Chapters 2 and 4, and Appendix A.

Problem Statement: Solve the following problems manually and then check your answers with the appropriate financial function in Excel.

NOTE: The financial functions themselves may not be enough to solve the problems. You may need to write equations that use the financial functions.

Part 1
NOMINAL function - What quarterly interest rate is equivalent to an effective annual rate of 8% per year, compounded quarterly?

Part 2
EFFECT function - What effective interest rate per quarter is equivalent to a nominal 12% per year, compounded monthly?

Part 3
RATE function - If $5000 is invested now in a franchise that promises the investment will be worth $10,000 in 3 years, what is the earned rate of return?

Part 4
PV function - How much money can you borrow now if you promise to repay the loan in 10 year-end payments of $3000, starting 1 year from now, if the interest rate is 18% per year?

Part 5
FV function - How much money will you have 12 years from now if your take your Christmas bonus of $2500 each year and buy shares in a stock mutual fund that earns 16% per year?

Part 6
NPV function - For the cash flows shown, calculate the present worth in year 0. Assume i = 14% per year.

Part 7
NPER function - How long will it take to repay a loan of $30,000, if you pay $2000 per year and the interest rate is 5% per year?

Parts 8-12: Based on the following
Jane is buying a new car. She agrees to a fixed payment-fixed interest loan of $25,000 at 8% per year compounded monthly to be repaid in 48 equal end-of-month payments.

8. PMT - Use this function to compute the monthly payment of the loan
9. IPMT - Use this function to compute the interest portion of the 10th payment
10. PPMT - Use this function to compute the principal portion of the 24th payment
11. CUMIPMT - Use this function to compute the cumulative interest paid between months 6 and 12
12. CUMPRINCE - Use this function to compute the cumulative principal reduction over the first 20 months

Helpful Comments: Excel Financial functions perform common business calculations, such as determining the payment for a loan, the future value or net present value of an investment, and the values of bonds or coupons. Common arguments for the financial functions include:

• Future value (fv)- the value of the investment or loan after all payments have been made. (F)
• Number of periods (nper)- the total number of payments or periods of an investment. (n)
• Payment (pmt)- the amount paid periodically to an investment or loan. (A)
• Present value (pv)- the value of an investment or loan at the beginning of the investment period. For example, the present value of a loan is the principal amount that is borrowed. (P)
• Rate (rate)- the interest rate or discount rate per period for a loan or investment. (i)
• Type (type)- the interval at which payments are made during the payment period, such as at the beginning of a month or the end of the month. End-of-period cash flows are usually assumed, so type = 0.

1. When using Excel functions, remember the sign convention is the same as when solving problems manually. Cash that you pay you, such as a deposit to savings, is represented by a negative number; cash you receive, such as a dividend check, is represented by a positive number. For example, a $1,000 deposit to the bank would be represented by the function argument -1000 if you are the depositor, and by the argument 1000 if you are the bank.

2. Rate and nper must be consistent (e.g., if nper = 48 months, then the interest rate must be given as interest per month)