Goal Seek VBA

[mitch1875]

Hi,

I am wanting to perform a goal seek on multiple cells, and want to automate the processing of this goal seek via VBA.

My sheet looks like the below.

Offer Rate	Offer Price	P & L	Contract Cashflows >>>> SUM OF MAR 21 ONWARDS		Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21
3.45%	£10,789.48	£0.00	£11,337.40		£324.22	£324.22	£324.22	£324.22	£324.22	£324.22	£324.22

I want to understand the minimum offer rate for a series of discounted cashflows on 2,000 rows of data (the Offer Price column has the following formula.

=NPV("Offer_Rate"/12,AL5:DZ5 ("SERIES OF CASHFLOWS")

So, i need to Goal Seek to find the Offer Rate for each series of cashflows, such that the above formula returns a break-even (i.e. the P&L) column is zero.

Is there some VBA code i could use rather having to manually goal seek each row.

Thansk

Dave

 

[joeu2004]

find the Offer Rate for each series of cashflows, such that the above formula returns a break-even (i.e. the P&L) column is zero

It sounds like you want to find the IRR: the "discount" rate of a series of cash flows such that their NPV is zero.

(Note: That assumes that P&L is indeed always zero. Is it?)

To that end, we can use the Excel IRR or RATE function instead of Goal Seek and VBA.

If all of the cash flows are equal, the Offer Rate might be:

=12*RATE(COUNT(AL5:DZ5), AL5, -AE5)

If the cash flows might vary, the Offer Rate might be:

{ =12*IRR(IF(COLUMN(AK5:DZ5)=COLUMN(AK5), -AE5, AK5:DZ5) }

Do not type the curly braces. They indicate that the formula is array-entered. Type everything except the curly-braces, then press ctrl+shift+Enter instead of just Enter.

In either course, format the cell as Percentage.

-----

But the details of your example are unclear; and my assumptions do not "add up" properly. My assumptions are:

1. The column labeled Mar-21 is AL, and the first cash flow is 342.22 in AL5.
2. The column labeled Offer Rate is AD, and 3.45% is in AD5. 10,789.48 is in AE5 and 11,337.40 is in AG5.
3. The value in AE5 is confirmed by the formula =NPV(AD5/12, AL5:DZ5) in another cell, to avoid a "circular reference" error.
4. The formula in AG5 is =SUM(AL5:DZ5).

Are those assumptions correct?

Are the cash flows starting in AL5 equal?

If they were, then:

1. There would be 35 equal cash flows of 342.22 in AL5:BT5 (=11337.40/AL5, rounded).
2. The Contract Cash Flows in AG5 would be =35*AL5, which is 11337.70, not 11337.40.
3. The Offer Price in AE5 would be =PV(3.45%/12, COUNT(AL5:DZ5), -AL5), which is 10780.7229937193, not 10789.48.
or
4. The Offer Rate in AD5 would be =12*RATE(COUNT(AL5:DZ5), AL5, -10789.48), which is 3.39482506363284%, not 3.45%.

Please clarify the details.

 

[joeu2004]

PS....

{ =12*IRR(IF(COLUMN(AK5:DZ5)=COLUMN(AK5), -AE5, AK5:DZ5) }
Do not type the curly braces. They indicate that the formula is array-entered. Type everything except the curly-braces, then press ctrl+shift+Enter instead of just Enter.

If you are willing to put the formula =-AE5 into AK5, that can be simplified. Normally-enter (press just Enter as usual) the following:

=12*IRR(AK5:DZ5)