Goal Seek VBA

mitch1875

New Member
Joined
Feb 4, 2016
Messages
6
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 RateOffer PriceP & LContract Cashflows >>>> SUM OF MAR 21 ONWARDSMar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-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
    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
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top