Goal seek with help of VBA

elmacay

Board Regular
Joined
May 4, 2006
Messages
88
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,209
Members
414,513
Latest member
junbuggle

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