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:
- The discount curve is calculated as (1+ spot rate) ^ (-year)
- 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.
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?