Microsoft® Excel Spreadsheet Exercise #4

Contributed by
Dr. Jeffrey Adler, P. E.
MindBox, Inc.

(formerly of Civil Engineering
Rensselaer Polytechnic Institute)

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.

Blank and Tarquin Text Reference: Chapter 4.

Helpful Comments: Recall the following functions used in Exercise #3:

• 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

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.

NOTES.
1. When using Excel functions, remember the sign convention is the same as when solving problems manually. Cash that you pay, 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)

Problem Statements: Use these formulas to solve the following problems.

a) If interest is not compounded, how many years will it take her investment to double?
b) How many years if interest is compounded yearly?
c) How many years if interest is compounded quarterly?

Problem #2: Sandra places $1000 in a savings account today and plans to invest $300 at the end of each quarter. Assume the bank pays 7.0% interest, compounded quarterly.

a) How many quarters will it take for Sandra's savings to exceed $6000?
b) How much would Sandra have to invest immediately if she wanted to reach her goal after two years?

a) What is Luis monthly payment?
b) What is the remaining principal on Luis' mortgage after making 5 years worth of mortgage payments?
c) What is the total interest that Luis has paid to date?

Problem #4: (Follow-on to Problem #3) Today interest rates have fallen to 7%. Luis wants to explore refinancing his home to lower his obligation. The mortgage company will charge Luis a flat fee of $3000 for refinancing his mortgage.

d) If Luis refinanced his mortgage today, what would be his new monthly payment?
e) Luis is considering relocating to a new city in the near future. How many more months would Luis have to live in his home to make the decision to refinance worthwhile? Assume the MARR is 8% per annum.
e) Solution by Hand: Difference in payments = $1074.61 - $944.15= $130.45

3000 = 130.45(P/A,0.08/12,n)

(P/A,0.00667,n) = 22.99

Determine n directly from the P/A factor formula solved for n.
<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif::Formula::/sites/dl/free/0072432349/22711/ex4.gif','popWin', 'width=228,height=72,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif">Formula (0.0K)</a>Formula
n = {-log[1-(0.00667)(22.99) }/ log (1.00667) = 25.04 (round up to 26 months)

---