University of Central Florida Economics Excel Questions

a.C.
MICP1.1.
What is the future worth of $1000 deposited today, 10 years
from now if the APR is
5% compounded yearly? Show interest earned and
account balance at the end of every year using a
spreadsheet. Copy the spreadsheet on to the word file for
submission. Check using a hand calculation. Show your
hand calculation results on your word file.
b. 5% compounded monthly? What is the Effective Annual
Interest rate in this case? Check using the formula and
also using a spreadsheet to calculate account balance at
the end of year if interest is compounded monthly. In
your world file show the spreadsheet calculation of the
Effective Interest Rate and the hand calculation as well.
Repeat part (a) for an APR of 10%. No need to show the
spreadsheet this time. What is difference in Future
Worth compared to 5% APR?
M1CP1.2.
Setup a spreadsheet to compute the Present Worth of the
following cash flows associated with a project. APR (Annual
percentage rate) is 5%. Assume yearly compounding.
Year
Cash Flow
0, Start of Project -20,000 (Negative to show
outflow)
1 (End of first year)
4000
2
4000
3
5000
4
7000
5
5000
6
5000
b. Draw the cash flow diagram using Excel. Copy the
spreadsheet table and graph to your word file for submission.
c. What is the discounted cash flow each year? Show in Excel
and then copy to word file.
d. What is the Payback Period with interest? Estimate by
plotting the Cumulative Discounted Cash Flow every year.
Summarize your calculations on your word file. Repeat your
calculation of Payback period if the APR is raised to 10%.
e. What is the Discounted Cash Flow Rate of Return? Compute
using Goalseek in Excel.
File
Home
Insert
Draw
Page Layout
Formulas
Data
Review
View
Help
6 Share
Comments
F18
f
0.1223
A
B
С C
D
E
F
G
H
J.
K
L
M
N
1
interest rate
0.05
Cash Flow
u WN
4
Problem CP1.2
$10,000.00
$5,000.00
6
Year
S-
7
1
2
3 4 5 6
$(5,000.00)
8
9
Cash Flow DCFK
Cum DCF
0 $(20,000.00) $ (20,000.00) $ (20,000.00)
1 $ 4,000.00 $ 3,809.52 $(16,190.48)
2 $ 4,000.00 $ 3,628.12 $(12,562.36)
3 $ 5,000.00 $ 4,319.19 $ (8,243.17)
4 $ 7,000.00 $ 5,758.92 $ (2,484.25)
5 $ 5,000.00 $ 3,917.63 $ 1,433.38
6 $ 5,000.00 $ 3,731.08 $ 5,164.45
Cum DCF
10
11
$10,000.00
12.
$5,000.00
3
$-
4.
1
2
3
5
6
7
Net Present Worth
$ 5,164.45
5
5 Profitability Indices
7
3
$(5,000.00)
$(10,000.00)
$(15,000.00)
$(20,000.00)
$(25,000.00)
Discounted Cash Flow Rate of Return
0.1223
Sheet1