Excel

Question

question 1 

sort score highest score first

d16=1

d17==IF(C16>C17,D16+1,D16)

copy paste all the way down 

the sort column A by section

e16==IF(A16<>A15,1,IF(C15>C16,E15+1,E15))

copy and paste all the way down

f16==VLOOKUP(C16,$J$16:$M$28,4)

copy paste al the way down

g16==IF(E16<=3, "A+", IF(E16<=10, "A", IF(E16<=20, "B", IF(E16<=40, "C", IF(E16<=45, "D", "F")))))

copy and paste all the way down

h16==IF(OR(F16="A+",G16="A+"),"A+",IF(OR(F16="A",G16="A"),"A", IF(OR(F16="A-",G16="A-"),"A-", IF(OR(F16="B+",G16="B+"),"B+", IF(OR(F16="B",G16="B"),"B", IF(OR(F16="B-",G16="B-"),"B-", IF(OR(F16="C+",G16="C+"),"C+", IF(OR(F16="C",G16="C"),"C", IF(OR(F16="C-",G16="C-"),"C-", IF(OR(F16="D+",G16="D+"),"D+", IF(OR(F16="D",G16="D"),"D", IF(OR(F16="D-",G16="D-"),"D-", “F"))))))))))))

copy paste all the way down 

Question 3

c11==(C8*(1+C9))/(C10-C9)

reference=(current dividend*(1+Growth rate))/(required return-growth rate)

c15===IF(C11<C7, "Overvalued", IF(C11=C7, "Fairly Valued", “Undervalued”)))

(C&P)c15=IF(C11<C7,”over valued",IF(C11=C7,"fairly valued",IF(C11>C7,"undervalued

reference=IF(Valuation<Stock Price,”overvalued”,  If(valuation=stockprice,”fairly Valued,”undervalued”))

Make Table

a27=year  b27=dividend          c27=terminal value   d27=pv            e27=year          f27= dividend

a28=1   b28=C20*(1+C21)      c28=0  d28=-PV($C$24,A28,0,B28+C28 )  1     f28=C20*(1+C21)

a29=2  b29=b28*(1+$C$21)   c29=0   d29=-PV($C$24,A29,0,B29+C29)   2   f29=f28*(1+$C$21)

a30=3  b30=b29*(1+$C$21)   c30=0   d30=-PV($C$24,A29,0,B29+C29)   3    f30= C&P all the

a31=4  b31=b30*(1+$C$21)   c31=B32/(C24-C22)      d31=C&P above     4               Way to 

a32=5  b32=b31*(1+$C$21)   c32=blank                      d32=blank              5           year 5                                                                                  

                                                                                                                     6 f33=f32*(1+$C$22)

                                                                                                                   7          f34=C&P

                                                                                                                   8           whole page

                                                                                                               year whole page

Reference (C31)= dividend year 5/(requred return-growth rate 2

reference (D28)= -PV($required$rate,year 1,0,dividend year1+ terminal value year1)

reference (F28)=Current dividend*(1+Growth rate 1)

reference(F29-F32)=C&P above

reference(F33)=Dividend year 5 *(1+$growth $rate 2)

reference(F34-whole page)= C&P above

C25==SUM(D28:D31)

d25==NPV(C24,F28:F1027)

reference(C25)=SUM(PVyear1 thru 4)

reference(D25)=NPV(Required Return,Dividend year1 thru 1000

question 4 

c14= given required rate in question (12%)

c15==-PV(C14/C9,C10*C9,C8*C7/C9,C7)

reference(C15)=-PV(Required Return Given/Payment frequency,Maturity*Payment frequency,Coupon rate*Par Value/Payment Frequency,Par Value)

c19==RATE(C9*C10,C8*C7/C9,-C6,C7)*2

reference(C19)=RATE(Payment Frequency*Maturity,Coupon Rate* Par Value/ Payment Frequency,-Price,Par Value)*2

d19== first make d6= 6/16/2016

           then make d7=6/16/2025

d19==YIELD(D6,D7,C8*100/C7,100,C9)

reference(D19)=YIELD(6/16/2016,6/16/2025,Coupon Rate *100/Par Value,100,Payment Frequency)

c23==DURATION(D6,D7,C8,C19,C9)

c24==MDURATION(D6,D7,C8,C19,C9)

reference(C23-C24)=Duration(Price,Par Value, Coupon Rate, Yield to Maturity, Payment freq)

h28-h53=0%-25%

i28==PRICE($D$6,$D$7,$C$8,H28,100,$C$9)*$C$7/100

i29-i53=C&P above

reference (I28)=PRICE($6/16/2016,$6/16,2025,$Coupon $Rate, H28,100,$payment $frequency)

Question 5

c13=12-5

c14=6%

c15=2

c16=1000

c17=750

c18==RATE(C13*C15,C14*C16/C15,-C17,C16)*2

c19==C18*(1-0.35)

refeerence(C18)=RATE(Maturity*Frequency,Coupon Rate*Par Value/Frequency,-Price,Par Value)*2

reference(C19)=Yield to Maturity*(1-.35(marginal tax rate )

g13=$5.00 given

g14=$70.00 given

g15==G13/G14%

reference(g15)COST of Preferred Equty=(Dividend/Price)%

A

k13=$1.25 given

k14=4% given

k15=$30 given

k16==(K13/K15)+K14

reference(k16)=(Dividend/Price)+growth rate

c24=C6/SUM($C$6:$C$) 

c25=C&P above

c26=C&P above

B

d30= Market value

d31==D6*K15

d32==D7*G14

d33==D8*C17

C31==D31/SUM($D$31:$D$33)

C32=C&P above

c33=C&Pabove

C

c38==K16%

c39===G15%

c40==C19%

D

c45==SUMPRODUCT(C24:C26,C38:C40)

c46===SUMPRODUCT(C31:C33,C38:C40)

Answer 1

Hi Will,

Before we proceed, I would require more information to assist you better.

1. The data mentioned in Question 1 are spread across the rows like E, F, G etc. how he like the sort to work with highest score first.?
2. While applying these formula in Excel what is the issue and where exactly it is causing the issue? 

Please get back to us with necessary information, we’ll be glad to assist you further.

Thank you.