Valuation

Create an Excel model that computes the value of preferred stock, common stock, and bonds. Note that on the template, this model is found under its own tab. $$Keep it this way. $$Please account for the following:

$[$Note: $$For input fields, enter the values given here. $$For calculation fields, YOU set up the spreadsheet to complete the correct calculation$]$

Preferred Stock Value Calculation:

Dividend$($D$)$: $($input field$)$Please enter the value $$1\mathrm{.}00$

Required Rate of Return$($r$)$: $($input field$)$ Please enter the value $8\mathrm{.}00\%$

Price$($P$)$: $($calculation field$).$Please use the formula: P$=$ D$/$r $.$

Common Stock Value Calculation:

Dividend$($D$)$: $($input field$)$Please enter the value $$1\mathrm{.}00$

Required Rate of Return$($r$)$: $($input field$)$ Please enter the value $10\mathrm{.}00\%$

Growth Rate$($g$)$: $($input field$)$ Please enter the value $5\mathrm{.}00\%$

Price$($P$)$: $($calculation field$).$Please use the formula: P$=$ D$/($r$-$g$).$

Bond Value Calculation:

Face Value$($FV$)$: $($input field$)$Please enter the value $$1,000.$

Coupon Rate: $($input field$)$ Please enter the value $9\mathrm{.}00\%$

Number of Periods $($NPER$)$: $($input field$)$ Please enter the value $20($years$)$

Yield$($RATE$)$: $($input field$).$Please enter the value $11\mathrm{.}00\%.$

Interest Payment$($PMT$)$: $($calculation field$).$Please use the formula: $($PMT $=$ Coupon Rate $*$ Face Value$).$

Price$($PV$)$: $($calculation field$).$Please use the PV function. $($Variable to use: $$FV$,$ PMT $($the coupon interest payment, NPER, RATE $($the yield$)).\backslash$begin$\{$tabular$\}\{|$l$|$l$|$l$|$l$|$l$|$l$|$l$|$l$|\}$

$\backslash$hline & A & B & C & D & E & F & G $\backslash \backslash$

$\backslash$hline $1$ & & Valuation Models & & & & & $\backslash \backslash$

$\backslash$hline $2$ & & & & & & & $\backslash \backslash$

$\backslash$hline $3$ & $\backslash$multicolumn$\{4\}\{|$l$|\}\{$Preferred Stock Valuation$\}$ & & & $\backslash \backslash$

$\backslash$hline $4$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Dividend $($D$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $5$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Required Rate $($r$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $6$ & & & & & & & $\backslash \backslash$

$\backslash$hline $7$ & & Price $($P$)$ & & & & & $\backslash \backslash$

$\backslash$hline $8$ & & & & & & & $\backslash \backslash$

$\backslash$hline $9$ & $\backslash$multicolumn$\{3\}\{|$l$|\}\{$Common Stock Valuation$\}$ & & & & $\backslash \backslash$

$\backslash$hline $10$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Dividend $($D$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $11$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Required Rate $($r$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $12$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Growth Rate $($g$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $13$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{\}$ & & & & $\backslash \backslash$

$\backslash$hline $14$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Price $($P$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $15$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{\}$ & & & & $\backslash \backslash$

$\backslash$hline $16$ & $\backslash$multicolumn$\{3\}\{|$l$|\}\{$Bond Valuation$\}$ & & & & $\backslash \backslash$

$\backslash$hline $17$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Face Value $($FV$)\}$ & & $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Coupon Rate$\}$ & $\backslash \backslash$

$\backslash$hline $18$ & $\backslash$multicolumn$\{3\}\{|$r$|\}\{$Interest PMT $($PMT$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $19$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$NPER$\}$ & & & & $\backslash \backslash$

$\backslash$hline $20$ & $\backslash$multicolumn$\{3\}\{|$c$|\}\{$Yield $($RATE$)\}$ & & & & $\backslash \backslash$

$\backslash$hline $21$ & & & & & & & $\backslash \backslash$

$\backslash$hline $22$ & & Price $($PV$)$ & & & & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$