# Goal seek with help of VBA

#### elmacay

##### Board Regular
Hi guys and girls,

In order to impress my CFO, who can't figure it out, I put myself in your hands, since I can't figure it out either, but he doesn't know that... :wink:

Here's what I want to do:

We have built a building for let's say 1,000,000. We want to know what the rent is that we have to ask in order to make the 1,000,000 as well, earn it back.
So I need the interest rate (5%), the number of years in which I want to earn it back (10), and the yearly increase in rent (2,5%).

I have made a table saying
year 1: rent / (1+interest rate) = net present value (NPV)
year 2: (rent + increase) / (1+interest rate)^2 = NPV
and so on for the next 8 years.

So I made me a nice little VBA

Code:
Range("D21").GoalSeek Goal:=1000000, ChangingCell:=Range("B5")
where B5 is the rent I have to charge.

Now here's the question, coz this is too limited
Can I automate the goal seeking thing where all things are variable? I want to be able to change the years, the interest rate and the increase in rent, which in the end comes back with the rent I have to charge.

I can't figure it out. Can anyone of you wizzards?

Greetz, Elmacay

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi
Paste the following codes in the macro window ( alt F11)

Code:
Sub Macro2()
Cells(1, 1) = "Rent"
Cells(1, 2) = "annual" & Chr(10) & "increase"
Cells(1, 3) = "Interest"
Cells(1, 4) = "Target"
For a = 1 To 8
rent = Cells(2, 1)
AI = Cells(2, 2)
Interest = Cells(2, 3)
Cells(2, 4) = Cells(2, 4) + rent + (a - 1) * AI / 100 / (1 + Interest / 100) ^ 2
Next a
End Sub
Fill cellsA2,B2 and C2 and run the macro. D2 gives cumulative value. you can vary the values and run the macro each time till you achieve the goal (goal seek does not work because target is not from a simple formula. it needs to loop for 8 years. -simply put, I don't know a formula which can do that) You can assign the macro to an object on the sheet to make it convenient to change values and observe its effect on the target value.
RAvi

Replies
0
Views
294
Replies
1
Views
371
Replies
1
Views
314
Replies
3
Views
445
Replies
0
Views
480

1,214,262
Messages
6,118,550
Members
448,835
Latest member
Profast123

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