Question

A: I need the answers to the question in the black square.
B: I need clarification on how the answers where found (in the blue cells)
And the most important is (What is the excel formula used to find the answer in the blue cells?)
.
.
And Its better if you solve it in Excel sheet then upload the picture here

STCWTE all all & 31 11:53 STC X Problem 5-4 You are considering two ways of financing a spring break vacation. You could put it on your credit card, at 15 % APR, compounded monthly, who want an 8% interest payment every six months. Which is the lower rate? or borrow the money from your parents, Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. 5 Credit card APR 15% Compounding perlods 12 8 % Six-month rate Compounding periods 2 EAR credit card EAR parents Which is lower? Requirements 1 In cell D11, by using cell references, calculate the EAR corresponding to borrowing on your credit card (1 pt.). 17 2 In cell D12, by using cell references, calculate the EAR corresponding to borrowing from your parents. Note: Notice that the rate in cell D8 is NOT expressed as an APR. (1 pt.) In cell D15, type either credit card or parents depending on which EAR is the lowest (1 pt.).

Answer 1

Two Ways of Financing spring break vacation:

1.Credit Card @ 15% APR, compounded monthly

2.Parents 8% interest Payment semi annually until paid

Basically we have to find out which ever is lower and go for it.

Let us assume you borrowed $1000 for the vacation for 1 Year.

1. Credit card: 15% APR to monthly i.e. 15/12 = 1.25% [ as the rate is in yearly we need a monthly converted rate ]

Principal = $1000 , Period(n)=12, interest(I)=1.25%

Future Value Paid = P * ( 1 + I ) ^ n

= 1000 * (1 + 0.0125)^12

= $1,160.75

EAR = [(1 + I) ^ n] - 1

= [(1 + 0.0825) ^ 12] - 1

= 16.07%

2. Parents: 8% interest to be paid

Principal = $1000 , Period(n)=2, interest(I)=8% semi annually [ as the rate is semiannually it need not require any conversion ]

Future Value paid = P * ( 1 + I ) ^ n

= 1000 * (1 + 0.08)^2

= $1,166.4

EAR = [(1 + I) ^ n] - 1

= [(1 + 0.08)^2] - 1

= 16.64%

It seems your parents are charging you a premium, go with your credit card you will end up saving $5.64.

All the above formula will work in the excel as it is.

Hope this helps. In case you find it helpful then give a thumbs up.