Emulate Excel's Goal Seek

[dcx693]

Crap. I did a copy and paste. That line of code should look like this:
XLPMT = obj.PMT(dblRate, lngNumPayments, dblPrincipal)

I got into the discussion towards the middle where you guys were talking about the PMT function, so that's what I concentrated on.

In Excel VBA there is a Range.GoalSeek method that you can automate from Access. Of course, the Range part of that method needs to be an Excel range, not an Access control value. You can create and manipulate Excel workbooks from Access, and I bet you could then use the GoalSeek on that. I haven't tried, however. I can try reading through some of the previous posts and seeing what you need. I'm sure some of the Excel GoalSeek functionality can be replicated in Access - if need be.

 

[dcx693]

On my field, I have fields for each of the following: monthly liabilites, loan amount, loan term, loan rate, income, proposed payment, TOTAL liabilities (monthly liabilites+proposed payment) and debt ratio (income/TOTAL liabilities). I want to input all fields except for loan amount. I then want a button that I will click and it will automatically set the debt ratio to a certain percent, by changing the loan amount, hence it will show the maximum that the borrower can afford as far as their debt ratio is concerned. Sorry, it was kind of hard to explain, but I hope this helps alot!!

Thanks

Actually, reading through that post made it pretty clear. This could certainly all be done in Access. It just requires using some VBA code to substitute values within a certain range. It would be particularly "easy" if you just needed to set the value of 1 cell just by changing 1 other cell (as opposed to multiple cells).

 

[RichO]

I guess my overall question is which figure do you want as a result of the calculation?

I was under the impression that you wanted to find out the monthly loan payment based on the principal, interest rate and term, in order to determine if the borrower qualifies for such a loan based on their debt to income ratio.

The word "goal seek" is foreign to me. Simply put, once you enter the Monthly Liabilities, Loan Term, Interest Rate, Borrower's Income, and Proposed Payment, are you wanting to find the maximum amount that can be borrowed?

 

[brian0721]

DCX, you got it right, I just need to change one cell by changing one other cell. I want to click a button, lets say for instance it says 45% as the caption. I would want this button to set one field (debt ratio) to 45% by changing cell (loan amount) or field, not cell. I can figure out the payment, I need to figure out the max loan amount that the borrower can afford, according to the debt ratio. Hope this helps explain more. Thanks a ton so far for the help guys!!

lemme know

 

[dcx693]

OK, but wait a sec. How is this a goal seek? It's simple multiplication. If I have liabilities of $100, payments of $100, and I want a debt to loan ratio of 20%, don't I just take the $100+$100 and divide the total by 20%? Yielding a loan amount of $1,000?

 

[brian0721]

OK, but wait a sec. How is this a goal seek? It's simple multiplication. If I have liabilities of $100, payments of $100, and I want a debt to loan ratio of 20%, don't I just take the $100+$100 and divide the total by 20%? Yielding a loan amount of $1,000?

lol, i wish it was that easy. The goal seek part is this: Adding up the current liabilites with the proposed loan payment. The thing is, there is millions of possibilites, well, maybe not millions, but you get the drift, TONS of possibilities for the loan amount. Each time the Loan Amount changes, the payment changes, and the debt to income ratio changes. The goal seek will automatically find the loan amount, that gives a payment, that when added to current liabilites, will be divided by the INCOME and give me the debt ratio that I want. This is confusing im sure...lol but maybe it helps???

lemme know

thanks again!

 

[dcx693]

OK, now I got it. I think. It shouldn't be too bad, but I've got to head home now. I'll work on it later or tomorrow.

 

[brian0721]

Thanks alot, almost time for me to go home also, but I'll be around later to check out the forum.

Thanks again, I really appreciate it

 

[brian0721]

maybe this will help...

 

[billyr]

Check your mail

Brian, check your email. I sent your form back to you - fixed.

 

[brian0721]

Brian, check your email. I sent your form back to you - fixed.

Checked it, form looks good, but I still need a way to make a button set the Debt Ratio to 45% by changing the loan amount. I'll email ya.

Thanks a ton!

 

[billyr]

Attached here too

Brian, its attached to this reply.

 

[brian0721]

Brian, its attached to this reply.

I gota get going home, I'll check it out then, You can yahoo messenger me at home, thanks alot!

 

[brian0721]

Ok, checked it out, i dont see a button to click to change loan amount, also, once you enter a number for each field, if you change it, it does not change anything, or maybe im missing something, let me know, ill be back later tonight.... thanks again!!!

 

[brian0721]

Any new suggestions? Still can't figure it out myself...ol

Thanks!

 

[billyr]

Wrong zip?

Brian, I must have sent you the wrong file. Its attached here - with the Calculate Button.

 

[brian0721]

Brian, I must have sent you the wrong file. Its attached here - with the Calculate Button.

THANK YOU SO MUCH!!!!!!!!!

That is exactly what I wanted. Thanks again!!!