Okay, I posted this before, but got no reply. so forgive me for re-posting but...
On my form I have a command button, what I need it to do, is essentially the exact same thing that Goal Seek does in Excel (make one text box a certain value by changing another text box). Any suggestions on the code for this?
How about a little more detailed explanation of what you would like to do. Some of us may not know what Goal Seek does and my Excel does not have it installed.
How about a little more detailed explanation of what you would like to do. Some of us may not know what Goal Seek does and my Excel does not have it installed.
It will mathematically figure out the max value a cell can be by changing another cell. For instance, in the case I want to put in Access. I have a mortgage payment calculator. I need to set the Loan Amount to the maximum it can be by changing a cell that has the borrower's income in it. DTI (Debt to Income Ratio) is what I am trying to accomplish. You can only by so much house for your income, this is what I am trying to do. Hope this helps.
So basically what you are saying is that you want to enter a figure into one field, and have another field on the form change it's value based on a calculation on the field you enter?
If so, what is the exact calculation you are doing? Give an example of a figure you want to enter and how you want Access to calculate that figure to come up with the desired result.
Unless my understanding of your question is way off base, this sounds like something that can easily be accomplished with VBA.
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!!
I follow what you are saying as far as wanting to determine how much of a monthly payment an individual can afford based on their income and debts. So you simply want Access to fill in the loan amount field based on a calculation of the other fields?
All I would need to know is the calculation that you use to come up with the loan amount figure, how you add/subtract/divide the other fields to come up with the result. Adding a command button and having VBA do the calculation is easy once I know the "formula".
I hate to put a fly in the ointment here but I recently had a problem raising a mortgage on an extra property here in the UK.
The reason was that no account was taken of my net asset worth.
E.g. I have saleable assets far in excess of the amount I wanted to borrow.
I wanted to purchase a property that had come on the market having previously had no plans to buy.
I needed to move quickly and, therefore, did not have the luxury of time to sell assets in order to raise the capitol.
But UK lenders did not seem to have the financial savvy to work out that I could more than afford to purchase (a) because the amount of money I needed exceeded my "DTI" (as you put it) and (B) the income from the profit on the purchase in just equity alone, exceeded the amount I was borrowing.
I think UK money lenders do not employ accountants.
I did get the money in the end by charging against other properties but it came from a foreign investor.
I made the purchase, six months later I put the property back on the market and more than doubled my money. Even after paying back the loan.
But my faith in the UK mortgage system dropped through the floor.
You seem to have made the same mistake. Someone’s declarable income is not always a simple math calculation.----- Sometimes other things need to be taken into account.
It would be nice, and sensible, to see these factors allowed for.
More fool they. They lost out on about 6k in easy money in six months. I know that’s peanuts to a big financial institution but look after the pennies.............
Oh don't get me wrong. As I said in my post I solved the problem but I had to go to an International rather than keep it at home. Re valuations are ok but most mortgage companies drag their feet if there is more than one property involved ---- The only way around it is to go to a high risk lender and self certify income and for that you are penalised very heavily. Also typical UK mortgage brokers don’t like tangible assets other than bricks and mortar. Why? The government is quick enough to take them into account.
The fact that the contents of the property consisted of 30% of the value of sale (they actually fetched more than that at auction) they also would not take into account. After having arranged the loan, within 24 hrs I was inundated with offers ------- How many where genuine I will never know. But the lack of foresight cost them money.
It seems to me that the mortgage lenders are beginning to take on the persona of the typical second hand car salesman. They are quick enough to lend money to people that can’t really afford it (in spite of all the clever calculations), hence all the TV adds for high risk lenders, but are completely blinkered if not totally blind to an honest deal.
I just needed some quick cash I had to buy while the going was good if I took too long the seller would have received offers and the potential profit would have dropped considerably as it was we had a deal "in principal" and the property locked out before anyone else got a look in.
The financier made a healthy profit, I made a years salary for my knowledge and the UK banks had a lunch of scrambled egg.
Debt to Income =[TOTAL monthly liabilities]/[income]
TOTAL Monthly Liabilites = [monthly liabilites] (i put in the number) +[monthly payment](of the new loan)
Parker... I see what you are saying, but as far as calculating net worth for this function, our investors don't even look at it. thanks for the input though
brian0721, can I ask why you want to do this in Access? If you want to do this in Access and the copy of Access is residing on a machine that also has Excel, then a solution is simply to make an automation call to Excel and have it do the calculation for you and return it to Access.
Good point, the reason as to doing it in access as opposed to excel, is basically that the people here are not computer literate at all and I am trying to keep everything in the same "Program" so that they cant possibly screw up, which they probably will anyways...lol, but if I can make it so that it does what you said, goto excel and return the value to access that would be great. I'm open to all suggestions. So how do I do this...?
Debt to Income =[TOTAL monthly liabilities]/[income]
TOTAL Monthly Liabilites = [monthly liabilites] (i put in the number) +[monthly payment](of the new loan)
Access VBA has the Pmt calculation built in but I'm not getting it to work like Excel says it's supposed to.
Assuming 10% interest, 36 month term, $100000 principal, Excel would return a result of $3227.
When I use these same figures in Access VBA it gives me a result of -100000, so I'm a bit confused. I'm going to post my own question in the VBA section and see what kind of information I can get. Stay tuned.
lol, but if I can make it so that it does what you said, goto excel and return the value to access that would be great. I'm open to all suggestions. So how do I do this...?
okay... what would I use as the obj though? The command button used to make the magic happen or the text box that will return the values. This is beyond my knoweldge lol, so i'll need all the help I can get.
Where do you want the answer to appear? If you're using a form, and you're entering the rate, the num of payments and the principal into the form, then you can have it calculate right on the form. No command buttons required.
Note: where you see bolded text, you need to replace those sample object names with the actual names from your database.
In form design view, go to the text box where you want the answer to appear. In the controlsource, type in:
where I assume that the control holding the rate is called txtRate, the control holding the number of payments is called txtNumPayments, and txtPrincipal holds the principal amount. Save the form.
Now go to the database window, and create a new Module. Enter the code below.
Code:
Function XLPMT(dblRate as Double, lngNumPayments as Long, _
dblPrincipal as Double) as Double
Dim obj As Object
Set obj = CreateObject("Excel.Application")
XLPMT = obj.PMT(dblRate as Double, lngNumPayments as Long, _
dblPrincipal as Double)
obj.Quit
End Function
No need to replace any of that code with names from your database. Type it in exactly as listed (better yet, just copy and paste it). The parameter names are generic. Save the module, and call it something like "basExcelPMTCall".
Go back to the form and open it. Enter your parameters (make sure to enter hte percentage rate as .08 or .12 - not as 8% or 12%). The answer should recalculate automatically. Note, if you need to divide the interest rate by 12 (for monthly payments), just change the controlsource formula slightly to:
Okay... I went through and did that, however part of the code doesnt work...
XLPMT = obj.PMT(dblRate as Double, lngNumPayments as Long, _
dblPrincipal as Double)
In any event, I have a question, how does this allow me to essentially goal seek, seeing as there is no where that I enter what exactly I want it to goal seek to. This is beyond my knowledge so I apologize if I have a few questions. I was thinking something along the lines of entering some information, then having a button to click that would Goal Seek to what I want.
