need this done in 4 hours

profileetenal
finance_formulas.xlsm
Home>Business & Finance homework help>need this done in 4 hours

Formula

Finance Formulas Inputs Output
1 FV of Annuity 2 PV of Annuity 3 Annuity Payment 4 PV: Growth Annuity 5 FV: Growth Annuity 6 PV: Perpetual Growth Ann.
Payment 100 Payment 2,000 PV 10,000 Payment 100 Payment 2,000 Payment 50
Annuity Pay/Year 12 Pay/Year 1 Pay/Year 1 Growth Rate 2.00% Growth Rate 5.00% Growth Rate 2.00%
Years 5 Years 20 Years 20 Pay/Year 1 Pay/Year 1 Pay/Year 2
Yield 5.00% Yield 5.00% Yield 3.00% Years 5 Years 5 Yield 5.00%
FV 6,801 PV 24,924 Payment 672 Yield 5.00% Yield 3.00% PV 3,333
or FV function 6,801 or PV Function 24,924 or PMT Function 672 PV 450 FV 11,701
7 FV of Cash Today 8 PV of Cash in Future 9 Forward Rate 10 IRR 11 NPV 12 Discounted PayBack
Cash Value Cash Today 1,000 Cash in Future 1,000 1-Year Rate 1.00% Cost -300 Cost -300 Cost -200 Acc. PV 200
Pay/Year 12 Pay/Year 12 2-Year Rate 1.50% CF1 50 CF1 50 1 CF1 50 -155 20
Years 5 Years 5 Forward Rate 2.00% CF2 50 CF2 50 2 CF2 50 -115 20
Yield 5.00% Yield 5.00% (1-year rate in year 2) CF3 50 CF3 50 3 CF3 50 -80 20
FV 1,283 PV 779 CF4 50 CF4 50 4 CF4 50 -48 20
CF5 50 CF5 50 5 CF5 50 -20 20
CF6 50 CF6 50 6 CF6 50 6 6
14 Bond Price 15 Yield to Maturity 16 Modified Duration CF7 50 CF7 50 7 CF7 50 28 7
Settle 3/1/15 Price 85.00 Settle 2/1/15 CF8 50 CF8 50 8 CF8 50 48 8
Maturity 3/1/20 Coupon 10% Maturity 2/1/25 CF9 50 CF9 50 9 CF9 50 66 9
Bonds Coupon 3.00% Pay/Year 2 Yield 5.00% CF10 50 CF10 50 10 CF10 50 83 10
Pay/Year 2 Par Amount 100.00 Coupon 5.0% IRR 10.56% Discount Rate 12% Disc.Rate 12%
Yield 2.00% Settle Date 2/1/15 Pay/Year 2 NPV (17) Disc Payback 6
Price 104.736 Maturity 2/1/27 Duration 7.79
YTM 12.44%
17 Balance at Period t 18 Monthly Payment 19 Months to Maturity
Principal 100,000 PV 100,000 Principal 100,000
Amortizing At Period t 36 Pay/Year 12 Interest Rate 5.00%
Loans Term 30 Years 10 Payment 1,888
Pay/Year 12 Yield 5.00% Pay/Year 12
Rate 4.50% Payment 1,061 Term (periods) 60.0
Balance 94,935 or PMT Function 1,061
20 Fair Value GE Data Sources: 21 Value: Dividend Model
Stock 10Yr Swap Rate 2.00% Swap Rate: http://www.federalreserve.gov/releases/h15/current/default.htm Dividend 0.92
Fair Value Risk Premium 8.50% Stocks: http://finance.yahoo.com/ Growth Rate 9.11%
Cap Rate 12.80% Individual Stock (GE) Pay/Year 4
EPS 1.50 Summary http://finance.yahoo.com/q?s=GE Swap Rate 2.00%
EPS Growth Rate 10.2% Projected EPS http://finance.yahoo.com/q/ae?s=GE+Analyst+Estimates Risk Premium 8.50%
Term 5
Eq.Grow 5.00%
Term 95
Beta 1.27 Beta 1.27
Market Price 25.0 S&P 500 (SPY) Cap Rate r 12.8%
Fair Value (P*) 25.0 Summary http://finance.yahoo.com/q?s=spy&ql=1 Market Price 25.0
-0.0 Projected EPS http://ycharts.com/indicators/reports/sp_500_earnings Value 25
-0
EPS Growth Rate:
Either input your "guess" or solve for the EXAMPLES
Implied Growth Rate (IGR) by clicking the EPS Beta Price Imp. Growth PE
button. The IGR is the EPS growth rate Nordstrom 3.77 1.34 79 18% 21
that equates the current market price GE 1.50 1.27 25 10% 17
and the Fair Value. CSX 1.92 1.14 36 9% 19
United Tech. 6.82 1.07 119 5% 17
UPS 3.28 1.04 101 18% 31
Apple 7.39 1.06 126 5% 17
CVS 3.96 1.01 103 13% 26
S&P 500 110 1.00 2085 5% 19
Monsanto 5.09 0.84 123 4% 24
Costco 4.81 0.83 148 10% 31
22 Multi-Year Returns 23 Expected Return & Volatility 24 WACC
Year Return Amount Return Probability Equity
0 100.0 45% 10% 1.0% Market Value 200
1 -50.0% 50.0 25% 25% 0.3% Risk Free 2.00%
2 50.0% 75.0 15% 30% 0.0% Risk Premium 8.0%
3 20.0% 90.0 5% 20% 0.1% Beta 1.25
4 5.0% 94.5 -20% 15% 1.7% Equity 12.00%
5 5.0% 99.2 Sum of Prob 100% Debt
Mean 6.0% (arithmatic) E(Return) 13.3% Market Value 20
Yield -0.2% (geometric) Variance 3.2% Debt Yield 3.00%
Volatility 17.8% Tax Rate 40%
Debt 1.80%
WACC 11.07%

Forward Rates

Yield Curves, Term Curves, and Forward Rates Source for Yield Curve:
http://www.federalreserve.gov/releases/h15/current/default.htm
Input Yield Curve Yield and Term Curves Forward Term Curve
( spot rates)
Yield Curve = Par Price Bond Yields Present Value Example: Forward Rates vs Single Yield
Maturity Rate Term Curve = Zero Coupon Bond Yields 6-Mth
6 Mth 0.36 Periods Term Discount Forward Rate Method Single Yield Method
1 Yr 0.42 Periods Years Yield Term Forward Rate Factors Cash Discount PV of Annuity
2 Yr 0.68 (semi) Curve Curve 0 0.0017983829 0.36 Flow Factors Payment 10
3 Yr 1.06 1 0.5 0.36 0.002 1.0018 0.36 1 0.0021003168 0.2402 0.48 0.9976 10 0.9976 Pay/Year 2
4 Yr 1.38 2 1.0 0.42 0.002 0.9982048455 1.0042 0.42 2 0.0027520678 0.41 0.81 0.9919 10 0.9919 Years 5
5 Yr 1.62 3 1.5 0.55 0.003 1.9940174089 1.0083 0.55 3 0.0034052486 0.54 1.08 0.9841 10 0.9841 Yield 1.62%
7 Yr 1.90 4 2.0 0.68 0.003 2.9858064412 1.0137 0.68 4 0.0043637148 0.82 1.65 0.9678 10 0.9678 PV 95.69
10 Yr 2.32 5 2.5 0.87 0.004 3.9723006191 1.0220 0.87 5 0.0053267108 1.02 2.04 0.9507 10 0.9507
6 3.0 1.06 0.005 4.9507647922 1.0324 1.07 6 0.0061413234 1.10 2.22 0.9362 10 0.9362
7 3.5 1.22 0.006 5.9193920146 1.0438 1.23 7 0.0069608062 1.27 2.56 0.9153 10 0.9153
8 4.0 1.38 0.007 6.8774396328 1.0571 1.40 8 0.0075774343 1.25 2.52 0.9052 10 0.9052
9 4.5 1.50 0.008 7.8234577742 1.0703 1.52 9 0.0081981368 1.38 2.78 0.8839 10 0.8839
10 5.0 1.62 0.008 8.7577744657 1.0851 1.65 10 0.0085586362 1.22 2.45 0.8861 10 0.8861
11 5.5 1.69 0.008 9.6793715561 1.0983 1.72 11 0.0089216417 1.29 2.60 0.8683 PV 94.19
12 6.0 1.76 0.009 10.5898870108 1.1125 1.79 12 0.0092872234 1.37 2.76 0.8495
13 6.5 1.83 0.009 11.4887856967 1.1277 1.87 13 0.0096554869 1.45 2.91 0.8298
14 7.0 1.90 0.010 12.3755494195 1.1440 1.94 14 0.010026564 1.52 3.07 0.8092
15 7.5 1.97 0.010 13.2496774834 1.1614 2.02 15 0.0104006082 1.60 3.23 0.7878
16 8.0 2.04 0.010 14.1106872144 1.1800 2.09 16 0.0107777915 1.68 3.39 0.7656
17 8.5 2.11 0.011 14.958114447 1.1999 2.17 17 0.0111583028 1.76 3.56 0.7427
18 9.0 2.18 0.011 15.7915139746 1.2211 2.24 18 0.0115423473 1.85 3.73 0.7192
19 9.5 2.25 0.011 16.610459961 1.2436 2.32 19 0.0119301465 1.93 3.90 0.6951
20 10.0 2.32 0.012 17.4145463149 1.2677 2.40 20 0.0123219384 2.02 4.08 0.6705
21 10.5 2.39 0.012 18.2033870254 1.2933 2.48

Put Call

1 INPUTS
Call & Put Pricing Model OUTPUTS
CALL PUT
Strike/P 102.6% 97.4% Ratio of Strike to Market Price
Date 3/15/15 P(Exer) 9% 12% Probability of Option Exercise
Stock SPY P* 0.17 0.25 Theoretical Price of Option
Market Price 195.0 Price 1.90 2.10 Actual Market Price of Option
Historical Vol 16.30% -1.73 -1.85
Interest Rate 2.00% Imp/Hist 1.01 1.16 Ratio of Implied Vol to Historical Vol
Option Expiry 3/20/15 Strike 200.0 190.0 Option Strike
0.01 Vol 16.4% 18.9% Volatility Input or Left click Calc button for Implied Vol
Model can solve for the price of a Call or Put, given the assumed interest rate volatility.
OR
Model can solve for the Implied Volatility, given the Market Price.
(left click on the Calc button)
Forward Months
CALL F PUT H F
Rate 1.020 Rate 1.02
Spot 195 Spot 195.00 Spot 195.00
Period 0.01 Period 0.01 Period 0.014
Rate(1+r) 1.020 Rate(1+r) 1.02 Rate(1+r) 1.02
Call 0.17 Put 0.25 Call 0.17
Strike 200 Strike 190 Strike 200
Strike PV 199.95 Strike PV 189.95 Strike PV 199.95
Vol 16.4% 18.9% Vol 16.4%
PUT 5.30
h= -1.3029 h= 1.1788 h= -1.303
Z 1.3029 Z 1.1788 Z 1.303
Prob= 0.9037 Prob= 0.8808 Prob= 0.904
Prob 0.0963 Prob 0.8808 Prob 0.096
h-st -1.3222 h-st 1.1566 h-st -1.322
Z 1.3222 Z 1.1566 Z 1.322
Prob= 0.9069 Prob= 0.8763 Prob= 0.907
Prob 0.0931 Prob 0.8763 Prob 0.093
0.1237