Cashflow Present Value Calculation

[Mandira13]

Hello there,

Please help me create a macro that calculates the present value of cash flows based on the dates of the cash flows and the maturity date of the instrument. I have absolutely no idea about how I should calculate it using a macro.
I have explained the details in the spreadsheet attached because it will make the context clearer.
Please help me.


Thank you so much in advance.

[SamT]

Point 1: Relevant Cash Flow Selection

1. Notice because Ins_1 Matures on the 19th, I have only used the cash flow which is dated before the maturity date i.e. the 16th July one.
2. For Ins_2, I have used the Cash Flows that are paid before 5th August 2013
3. I need the macro to factor the dates of the cash flows and the maturity dates of the instrument when computing the present value.

How do we know when to use an arbitrary date like July 16 or the maturity date or today's date?


Where did July 16 come from? The only place it is shown on the sheet is in A1:B6 and no other date from that range is used.


Point 4: Overall Computation of the Value of the instrument

1. The Desired Value of each instrument, should be the sum of the PV of the relevant cash flows. For Ins_1, it was just one cash flow, for Ins_2, it is the sum of the two relevant cash flows and so forth.
2. The Cash Flows that should be summed should meet the criteria in Point 1.

How to we know how many cash flows an instrument has? You have only shown one instance of each instrument in F1:J6.


In C10:D25, you seem to be adding each instruments sums together in each succeeding instruments CF, but the numbers in the succeeding instrument don't exactly match the preceding sums.


Of all the date and numbers in A1:B6, only the July 16 value matches anything else in the sheet.

Where are the initial values of each Instrument? It's hard to compute without a starting value

[snb]

In I2 the array formula:

=SUM(B$2:B2/(1+$H2)^((A$2:A2-$F2)/365))

until in I6

=SUM(B$2:B6/(1+$H6)^((A$2:A6-$F6)/365))