Inconsistent excel formula

PRANAY1395 

How can I make corrections when I don't know the concept behind the calculations?

However, I will show you the differences between the calculations on each column so you may figure you where you might have made a mistake in your calculations:

for example:

check if these calculations are correct.

 

Price 
E 
= D20/(1+(D3/2))^B3 
= D20/(1+(D4/2))^B4 
=((D20*(C5/2))/(1+(D5/2))^B3)+((D20*(C5/2))/(1+(D5/2))^B4)+((D20+(D20*(C5/2)))/(1+(D5/2))^B5) 
=((D20*(C6/2))/(1+(D6/2))^B3)+((D20*(C6/2))/(1+(D6/2))^B4)+((D20*(C6/2))/(1+(D6/2))^B5)+((D20+(D20*(C6/2)))/(1+(D6/2))^B6) 
=((D20*(C7/2))/(1+(D7/2))^B3)+((D20*(C7/2))/(1+(D7/2))^B4)+((D20*(C7/2))/(1+(D7/2))^B5)+((D20*(C7/2))/(1+(D7/2))^B6)+((D20+(D20*(C7/2)))/(1+(D7/2))^B7) 
=((D20*(C8/2))/(1+(D8/2))^B3)+((D20*(C8/2))/(1+(D8/2))^B4)+((D20*(C8/2))/(1+(D8/2))^B5)+((D20*(C8/2))/(1+(D8/2))^B6)+((D20*(C8/2))/(1+(D8/2))^B7)+((D20+(D20*(C8/2)))/(1+(D8/2))^B8) 
=((D20*(C9/2))/(1+(D9/2))^B3)+((D20*(C9/2))/(1+(D9/2))^B4)+((D20*(C9/2))/(1+(D9/2))^B5)+((D20*(C9/2))/(1+(D9/2))^B6)+((D20*(C9/2))/(1+(D9/2))^B7)+((D20*(C9/2))/(1+(D9/2))^B8)+((D20+(D20*(C9/2)))/(1+(D9/2))^B9) 
=((D20*(C10/2))/(1+(D10/2))^B3)+((D20*(C10/2))/(1+(D10/2))^B4)+((D20*(C10/2))/(1+(D10/2))^B5)+((D20*(C10/2))/(1+(D10/2))^B6)+((D20*(C10/2))/(1+(D10/2))^B7)+((D20*(C10/2))/(1+(D10/2))^B8)+((D20*(C10/2))/(1+(D10/2))^B9)+((D20+(D20*(C10/2)))/(1+(D10/2))^B10) 
=((D20*(C11/2))/(1+(D11/2))^B3)+((D20*(C11/2))/(1+(D11/2))^B4)+((D20*(C11/2))/(1+(D11/2))^B5)+((D20*(C11/2))/(1+(D11/2))^B6)+((D20*(C11/2))/(1+(D11/2))^B7)+((D20*(C11/2))/(1+(D11/2))^B8)+((D20*(C11/2))/(1+(D11/2))^B9)+((D20*(C11/2))/(1+(D11/2))^B10)+((D20+(D20*(C11/2)))/(1+(D11/2))^B11) 
=((D20*(C12/2))/(1+(D12/2))^B3)+((D20*(C12/2))/(1+(D12/2))^B4)+((D20*(C12/2))/(1+(D12/2))^B5)+((D20*(C12/2))/(1+(D12/2))^B6)+((D20*(C12/2))/(1+(D12/2))^B7)+((D20*(C12/2))/(1+(D12/2))^B8)+((D20*(C12/2))/(1+(D12/2))^B9)+((D20*(C12/2))/(1+(D12/2))^B10)+((D20*(C12/2))/(1+(D12/2))^B11)+((D20+(D20*(C12/2)))/(1+(D12/2))^B12) 
=((D20*(C13/2))/(1+(D13/2))^B3)+((D20*(C13/2))/(1+(D13/2))^B4)+((D20*(C13/2))/(1+(D13/2))^B5)+((D20*(C13/2))/(1+(D13/2))^B6)+((D20*(C13/2))/(1+(D13/2))^B7)+((D20*(C13/2))/(1+(D13/2))^B8)+((D20*(C13/2))/(1+(D13/2))^B9)+((D20*(C13/2))/(1+(D13/2))^B10)+((D20*(C13/2))/(1+(D13/2))^B11)+((D20*(C13/2))/(1+(D13/2))^B12)+((D20+(D20*(C13/2)))/(1+(D13/2))^B13) 
=((D20*(C14/2))/(1+(D14/2))^B3)+((D20*(C14/2))/(1+(D14/2))^B4)+((D20*(C14/2))/(1+(D14/2))^B5)+((D20*(C14/2))/(1+(D14/2))^B6)+((D20*(C14/2))/(1+(D14/2))^B7)+((D20*(C14/2))/(1+(D14/2))^B8)+((D20*(C14/2))/(1+(D14/2))^B9)+((D20*(C14/2))/(1+(D14/2))^B10)+((D20*(C14/2))/(1+(D14/2))^B11)+((D20*(C14/2))/(1+(D14/2))^B12)+((D20*(C14/2))/(1+(D14/2))^B13)+((D20+(D20*(C14/2)))/(1+(D14/2))^B14) 
=((D20*(C15/2))/(1+(D15/2))^B3)+((D20*(C15/2))/(1+(D15/2))^B4)+((D20*(C15/2))/(1+(D15/2))^B5)+((D20*(C15/2))/(1+(D15/2))^B6)+((D20*(C15/2))/(1+(D15/2))^B7)+((D20*(C15/2))/(1+(D15/2))^B8)+((D20*(C15/2))/(1+(D15/2))^B9)+((D20*(C15/2))/(1+(D15/2))^B10)+((D20*(C15/2))/(1+(D15/2))^B11)+((D20*(C15/2))/(1+(D15/2))^B12)+((D20*(C15/2))/(1+(D15/2))^B13)+((D20*(C15/2))/(1+(D15/2))^B14)+((D20+(D20*(C15/2)))/(1+(D15/2))^B15) 
=((D20*(C16/2))/(1+(D16/2))^B3)+((D20*(C16/2))/(1+(D16/2))^B4)+((D20*(C16/2))/(1+(D16/2))^B5)+((D20*(C16/2))/(1+(D16/2))^B6)+((D20*(C16/2))/(1+(D16/2))^B7)+((D20*(C16/2))/(1+(D16/2))^B8)+((D20*(C16/2))/(1+(D16/2))^B9)+((D20*(C16/2))/(1+(D16/2))^B10)+((D20*(C16/2))/(1+(D16/2))^B11)+((D20*(C16/2))/(1+(D16/2))^B12)+((D20*(C16/2))/(1+(D16/2))^B13)+((D20*(C16/2))/(1+(D16/2))^B14)+((D20*(C16/2))/(1+(D16/2))^B15)+((D20+(D20*(C16/2)))/(1+(D16/2))^B16) 
=((D20*(C17/2))/(1+(D17/2))^B3)+((D20*(C17/2))/(1+(D17/2))^B4)+((D20*(C17/2))/(1+(D17/2))^B5)+((D20*(C17/2))/(1+(D17/2))^B6)+((D20*(C17/2))/(1+(D17/2))^B7)+((D20*(C17/2))/(1+(D17/2))^B8)+((D20*(C17/2))/(1+(D17/2))^B9)+((D20*(C17/2))/(1+(D17/2))^B10)+((D20*(C17/2))/(1+(D17/2))^B11)+((D20*(C17/2))/(1+(D17/2))^B12)+((D20*(C17/2))/(1+(D17/2))^B13)+((D20*(C17/2))/(1+(D17/2))^B14)+((D20*(C17/2))/(1+(D17/2))^B15)+((D20*(C17/2))/(1+(D17/2))^B16)+((D20+(D20*(C17/2)))/(1+(D17/2))^B17) 
=((D20*(C18/2))/(1+(D18/2))^B3)+((D20*(C18/2))/(1+(D18/2))^B4)+((D20*(C18/2))/(1+(D18/2))^B5)+((D20*(C18/2))/(1+(D18/2))^B6)+((D20*(C18/2))/(1+(D18/2))^B7)+((D20*(C18/2))/(1+(D18/2))^B8)+((D20*(C18/2))/(1+(D18/2))^B9)+((D20*(C18/2))/(1+(D18/2))^B10)+((D20*(C18/2))/(1+(D18/2))^B11)+((D20*(C18/2))/(1+(D18/2))^B12)+((D20*(C18/2))/(1+(D18/2))^B13)+((D20*(C18/2))/(1+(D18/2))^B14)+((D20*(C18/2))/(1+(D18/2))^B15)+((D20*(C18/2))/(1+(D18/2))^B16)+((D20*(C18/2))/(1+(D18/2))^B17)+((D20+(D20*(C18/2)))/(1+(D18/2))^B18) 

check these formulas in column E are they correct?

 
Discount 
Factor 
F 
=1/(1+D3/2)^B3 
=1/(1+D4/2)^B4 
=+(E5 - (( C5 / 2 * $D$20 ) * ( G4 ))) / ( $D$20 + ( $D$20 * C5 / 2 )) 
=+(E6 - (( C6 / 2 * $D$20 ) * ( G5 ))) / ( $D$20 + ( $D$20 * C6 / 2 )) 
=+(E7 - (( C7 / 2 * $D$20 ) * ( G6 ))) / ( $D$20 + ( $D$20 * C7 / 2 )) 
=+(E8 - (( C8 / 2 * $D$20 ) * ( G7 ))) / ( $D$20 + ( $D$20 * C8 / 2 )) 
=+(E9 - (( C9 / 2 * $D$20 ) * ( G8 ))) / ( $D$20 + ( $D$20 * C9 / 2 )) 
=+(E10 - (( C10 / 2 * $D$20 ) * ( G9 ))) / ( $D$20 + ( $D$20 * C10 / 2 )) 
=+(E11 - (( C11 / 2 * $D$20 ) * ( G10 ))) / ( $D$20 + ( $D$20 * C11 / 2 )) 
=+(E12 - (( C12 / 2 * $D$20 ) * ( G11 ))) / ( $D$20 + ( $D$20 * C12 / 2 )) 
=+(E13 - (( C13 / 2 * $D$20 ) * ( G12 ))) / ( $D$20 + ( $D$20 * C13 / 2 )) 
=+(E14 - (( C14 / 2 * $D$20 ) * ( G13 ))) / ( $D$20 + ( $D$20 * C14 / 2 )) 
=+(E15 - (( C15 / 2 * $D$20 ) * ( G14 ))) / ( $D$20 + ( $D$20 * C15 / 2 )) 
=+(E16 - (( C16 / 2 * $D$20 ) * ( G15 ))) / ( $D$20 + ( $D$20 * C16 / 2 )) 
=+(E17 - (( C17 / 2 * $D$20 ) * ( G16 ))) / ( $D$20 + ( $D$20 * C17 / 2 )) 
=+(E18 - (( C18 / 2 * $D$20 ) * ( G17 ))) / ( $D$20 + ( $D$20 * C18 / 2 )) 

Calculations in column F check their correctness

 Sum of current 
and prior period 
discount factors 
G 
=+F3 
=+F4+F3 
=G4+F5 
=G5+F6 
=G6+F7 
=G7+F8 
=G8+F9 
=G9+F10 
=G10+F11 
=G11+F12 
=G12+F13 
=G13+F14 
=G14+F15 
=G15+F16 
=G16+F17 
=G17+F18 

Column G

 Theoretical 
Semi-Annual 
Spot Rate 
H 
=(+D3/2*100)% 
=+D4/2 
=(1/F5^(1/B5))-1 
=(1/F6^(1/B6))-1 
=(1/F7^(1/B7))-1 
=(1/F8^(1/B8))-1 
=(1/F9^(1/B9))-1 
=(1/F10^(1/B10))-1 
=(1/F11^(1/B11))-1 
=(1/F12^(1/B12))-1 
=(1/F13^(1/B13))-1 
=(1/F14^(1/B14))-1 
=(1/F15^(1/B15))-1 
=(1/F16^(1/B16))-1 
=(1/F17^(1/B17))-1 
=(1/F18^(1/B18))-1 

Column H

 Theoretical
Annual 
Spot Rate 
I 
=+H3*2 
=+H4*2 
=+H5*2 
=+H6*2 
=+H7*2 
=+H8*2 
=+H9*2 
=+H10*2 
=+H11*2 
=+H12*2 
=+H13*2 
=+H14*2 
=+H15*2 
=+H16*2 
=+H17*2 
=+H18*2 

Column I

Implied 
Semi-Annual 
Forward Rates 
J 
=+(((1+H$4)^(B$4)/(1+H$3)^((B$3))-1)) 
=+(((1+H5)^(B4+1)/(1+H4)^(B4))-1) 
=+(((1+H6)^(B5+1)/(1+H5)^(B5))-1) 
=+(((1+H7)^(B6+1)/(1+H6)^(B6))-1) 
=+(((1+H8)^(B7+1)/(1+H7)^(B7))-1) 
=+(((1+H9)^(B8+1)/(1+H8)^(B8))-1) 
=+(((1+H10)^(B9+1)/(1+H9)^(B9))-1) 
=+(((1+H11)^(B10+1)/(1+H10)^(B10))-1) 
=+(((1+H12)^(B11+1)/(1+H11)^(B11))-1) 
=+(((1+H13)^(B12+1)/(1+H12)^(B12))-1) 
=+(((1+H14)^(B13+1)/(1+H13)^(B13))-1) 
=+(((1+H15)^(B14+1)/(1+H14)^(B14))-1) 
=+(((1+H16)^(B15+1)/(1+H15)^(B15))-1) 
=+(((1+H17)^(B16+1)/(1+H16)^(B16))-1) 
=+(((1+H18)^(B17+1)/(1+H17)^(B17))-1) 

Column J

 Implied 
Annual 
Forward Rates 
K 
=+J3*2 
=+J4*2 
=+J5*2 
=+J6*2 
=+J7*2 
=+J8*2 
=+J9*2 
=+J10*2 
=+J11*2 
=+J12*2 
=+J13*2 
=+J14*2 
=+J15*2 
=+J16*2 
=+J17*2 

Column K

Ask yourself this, Why do some rows not have consistent formulas?

cheers

PRANAY1395 

I wouldn't worry about that. I guess the warning is because the formula refers to the following row as well as to the row itself. But that is intentional.

You can shorten the formula to

=(1+H4)^B4/(1+H3)^B3-1