Discount Curve Formula

[Shawn]

Hi,

I've just inherited a spreadsheet that is calculating a discount curve
from spot rates and I'm not sure what the formula is doing. I've uploaded
a copy of the spreadsheet to:

www.songrequest.fsnet.co.uk/Curve.xls

I can follow what is being done calculating the discount factors upto the
1 year point but after that they are including the sum of the figures in
the next column. Am I missing something really obvious or is there a
reason why the same formula isn't used for all time periods?

Thanks in advance for any advice.

Shawn

 

[Sandy Mann]

I assume that you have had no replies because most people will not open
spreadsheets in case of harmful code etc. Try explaining you question in
words and I am sure that you will get an answer.

--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Shawn]

I assume that you have had no replies because most people will not open
spreadsheets in case of harmful code etc. Try explaining you question in
words and I am sure that you will get an answer.

I should have realised that there might be an issue with people not
wanting to open a spreadsheet. Ok here goes:

In column B (cells 11 to 104) I've got a list of dates (from today out to
40 years time) which initially go in 1 month intervals up to 1 year and
then in 6 monthly intervals after that. Column C has a list of spot rates
for these corresponding dates. Column D has the discount curve where for
example the formula in D12 is: =1/(1+(C12/100)*(B12-$B$11)/365) which
makes sense as it is just working out the discount rate from the spot rate
in C12 and the dates in B11 & B12. This formula is used upto the 1 year
point. From then on it changes which is what I don't understand.

In Cell E20 (corresponding to 6 months) there is the following formula:
=D20*(B20-$B$11)/365 which is then repeated from the 1 year point onwards.
After the 1 year point the discount rate in column D is then calculated
using the following formula:
=(1-(SUM($E$20,$E$26:E33))*C34/100)/(1+(C34/100)*(B34-B33)/365)

This is the bit that has lost me. I don't see why the original formula
couldn't be used for all time periods instead of including the sum of the
these values in column E.

 

[Sandy Mann]

mmmm....

It seems that you are not having any better luck. Perhaps it is because
people do not understand what it is that the formulas are supposed to be
calculating - I certainly don't - or perhaps it is because your question is
not really a Excel question, perhaps more of an accounting question. A
fuller explanation of what the sheet is supposed to be doing may - or there
again may not - succeed in getting an answer.

--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk