0

Please let me know if this question isn't appropriate for the Math stack exchange

Here is a simple example of calculating the present value of some cashflows using a yield curve that I've made up:

enter image description here

  1. The discount curve is calculated as (1+ spot rate) ^ (-year)
  2. The present value (PV) is calculated by summing the product of the discount curve multiplied by the cashflow at each time period (it's a simple SUMPRODUCT formula in Excel)

My problem arises in that I often have to calculate a fixed addition to the spot rate (at all time periods) that provides for a specific PV. For example, below I have solved that to get a PV of 10.5 instead of 10.802 I need to add 0.96% to each spot rate.

enter image description here

I calculate this addition using a goalseek in Excel but it's time consuming because we have to do this many times. Is there any way to make a mathematical approximation for this type of calculation?

CallumDA
  • 173
  • There are ways to approximate the increment so that the present value is equal to the desired present value up to some digits after decimal point (in your case, 3 digits). But all such numerical methods in my knowledge are iterative -- you start with a "guess" increment value, changing it progressively until you arrive at a satisfactory approximation. If done manually, they take a lot of time. Besides, Goal Seek automates precisely this iterative algorithm. – Shubham Johri Jul 02 '20 at 07:41
  • You could try this out though: maintain a cell, say $A1$, holding the increment value initially set to 0. Then type in this formula for calculating the discount values:$$d_i=(1+s_i+$A$1)^{-i}$$$i$ being the year, $s_i,d_i$ its corresponding spot-rate and discount-value. Now when you Goal Seek, you wouldn't need to add additional columns and the process will just take seconds. – Shubham Johri Jul 02 '20 at 07:47
  • Hi @ShubhamJohri, thanks but this is exactly why I'm asking the question -- are there any non-iterative methods? Your second comment describes my current method which I'm hoping to improve. – CallumDA Jul 02 '20 at 09:01
  • @CallumDA No, there is no non-iterative method. But Excel is perfect for iterative methods like the Newton-Raphson method. – callculus42 Jul 02 '20 at 10:32

0 Answers0