Question

Case 6.1: Charleston Painless Surgery Center Key concept:

Time value of money and Excel functions to automate computations Meredith Becker was born in Charleston, South Carolina, but she traveled upstate to earn a bachelor’s degree in business from Clemson University. After graduation, she obtained a job at the Medical University of South Carolina (MUSC) and subsequently received her master’s degree from MUSC’s executive MHA program. After six years at MUSC with a wide variety of successful assignments, Meredith became chief operating officer of Charleston Painless Surgery Center (CPSC), a specialty surgery center owned by six physicians. Soon after starting at CPSC, Meredith found herself facing a series of decisions involving time value analysis. You are to assume the role of her MHA intern and see how you, with the tools available in Microsoft Excel, can help her with these decisions.

Questions 1. Charleston Painless Surgery Center currently has $100,000 in excess cash. Meredith wants to invest the cash in a three-year bank certificate of deposit (CD) where, at maturity, the buyer receives the principal plus accumulated interest.

a. What is the future value of the CD if it pays 2 percent interest compounded annually? 1 percent? 3 percent? (Hint: Use Excel’s “FV” function.)

b. Big Bank offers a CD with 3 percent nominal (stated) interest that is compounded monthly. What is the effective annual rate on this CD? (Hint: Use Excel’s “Effect” function.) What will the value be in three years if the $100,000 is invested in this CD? (Hint: Use Excel’s “FV” function.)

Answer 1

(a) (1) : at 2% annual compounding the future value of CD will be: FV(2%, 3,, -100000)

= $106,120.80

(2): at 1% annual compounding the future value will be: FV(1%, 3,, -100000)

= $103,030.10

(3): at 3% annual compounding the future value will be: FV(3%, 3,, -100000)

= $109,272.70

(b): Here the effective annual rate will be: EFFECT(3%, 12)

= 3.041596%

Using this rate we get future value as: FV(3.041596%, 3,, -100000)

= $109,405.14