Borrowing Power (2003 allSPs)

Topic

New Reply

I need to have a function that can calculate the amount a person can borrow based on their Income. I want to calculate a set % of the income as the maximum payments they could afford each month, and based on that and a set loan period and interest rate, calculate what their borrowing power is
TIA
Steve

Reply | Quote

Replies

VBA has built-in financial functions. The one you need is probably PV (present value). Its syntax is

PV(rate, nper, pmt, fv, type)

rate is the interest rate (for example 0.06 if the rate is 6%)
nper is the number of periods (the duration of the loan)
pmt is the payment
fv is the future value, if you omit it, 0 is assumed (the default for a loan – no debt left at the end)
type is either 0 if the payments fall at the end of each period, or 1 if the payments fall at the beginning; if you omit it, 0 is assumed (the default for a loan)

Note: if nper is a number of years, rate must be the yearly interest rate, and pmt the yearly payment; if nper is a number of months, rate is the monthly interest rate and pmt the monthly payment, etc.

Note2: the result of PV will have the opposite sign of pmt; if you want a positive result, make pmt negative.

Example: 6% interest during 20 years, with a yearly payment of 10% of $50,000:
PV(0.06, 20, -0.10*50000)

Reply | Quote

Thanks yet again Hans (when do you ever sleep ?)
I had understood the PV function was probably what I need but am lost on the VBA side
Steve

Reply | Quote

You can use PV (like most VBA functions) in a query too, and in the control source of a text box on a form or report.

Reply | Quote

Excellent – Thanks Again
Steve

Reply | Quote

Hans,
minorish issue
While I can build the interest rate and loan period into the query, is there a way to have a form where these are entered then used for the calculation. What’s throwing me is there is no actual link to the individual record, just a variable I’d like to be able to set in one place and re-use but also be able to change to see the flow through effect on the amount that can be borrowed. Hope this makes sense
Steve

Reply | Quote

One solution is to perform the calculations in the form. You can have controls bound to fields from the record source (such as income), and unbound controls for the values that are the same for each record (such as interest rate).

You could also create a table with fields for interest rate etc., with just one record. Set the values of the unbound controls on the form in the On Load event of the form, using DLookups.

Reply | Quote