# Goal Seek VBA

#### mitch1875

##### New Member
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

#### Attachments

• 1615470247655.png
3 KB · Views: 6

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### joeu2004

##### Well-known Member
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%.

#### joeu2004

##### Well-known Member
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)

Replies
3
Views
79
Replies
2
Views
61
Replies
12
Views
114
Replies
4
Views
391
Replies
5
Views
242

1,129,791
Messages
5,638,329
Members
417,021
Latest member
moon miner

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back