Accounting Application with MS Access ?

[Guest]

Sorry about reposting this thread. I'm stuck up on the following issues in an
Accounting Application I'm trying to develop with MS Access.

Are there any solutions in Access VBA (built-in or otherwise) to:
---> Calculate EMI (Equated Monthly Installment)
---> Calculate Loan Balance after 'n'th period after all the 'n' EMIs have
been paid.

Please help. Please let me know if it can be done or not. Any help is
greatly appreciated as ever.

(As suggested earlier by this newsgroup, I'm writing an elaborate
specification for this App and my success or failure in resolving the above
issues will decide whether I can continue with this one or not.)

 

[Tim Ferguson]

Are there any solutions in Access VBA (built-in or otherwise) to:
---> Calculate EMI (Equated Monthly Installment)
---> Calculate Loan Balance after 'n'th period after all the 'n' EMIs
have been paid.

Please help. Please let me know if it can be done or not. Any help is
greatly appreciated as ever.

This is not really an Access question: it's maths. You could try looking
for a mathematical modelling newsgroup. Or google for algorithms. Or look
at the hundreds of javascript calculators on the web, although the
problem with them is that, as you've found, they are not necessarily
based on good maths.

If I were you, I'd be looking at applied maths textbooks in bookshops.
Another source would be the local university library, either under
numerical methods and algorithms or simple programming courses. If my job
depended on it, I'd be asking to be sent on the whole course.

Hope that helps


Tim F

 

[Guest]

You are right about the nature of this question.

I just thought that since MS Excel offers PMT and FV functions that do
something similar, I somehow felt that there will be an Access variant of
these functions that can be easily adopted to a database model.
I have two options here :
--- 1) I've got a formula : EMI = P×r×(1 + r)n/((1 + r)n - 1), which I can
tweak to suit my db structure -
--- 2) or else - simply use the Excel functions (with enough tweaking, again).
Just looking for a probable better solution.

Thank you very much for the reply.

 

[Guest]

Yes there is a PMT and FV function in Access. Just search under help for the
parameters.

 

[Tim Ferguson]

--- 1) I've got a formula : EMI = PÇ-rÇ-(1 + r)n/((1 + r)n - 1), which
I can tweak to suit my db structure -

The trouble with using formulae like this is that when they move from
algebra to the real digital world, the order of calculation starts to
become very important. What is rounded and when and how that is cascaded
can make big differences to the final answer. That is why you need a tried
and tested _method_ rather than chucking a multiple-stage calculation at a
processor and crossing your fingers.

This might be getting a bit closer:

http://www.codeproject.com/netcf/BNMortCalc.asp

Best of luck


Tim F

 

[Guest]

Thanks for the reply.

I've downloaded the file from the link you've given but cannot run it.

It says "The selected file is not a valid Visual Studio Solution file"

 

[Johnny M]

The following is the methodology for using an Excel function inAccess
Using the standard setup you cannot use an excel function within a query.
However, you can mask a function and borrow excel functionality to achieve
the same result.



Create a new module or open an existing one. Paste the following code in.

Public Function ExcelCosH(Angle As Double) As Double
ExcelCosH = Excel.WorksheetFunction.Cosh(Angle)
End Function




Add a reference to Microsoft Excel, and then modify the function line to the
particular function you need. The one I've shown is the CosH function,
although you can use almost any of the excel functions availiable. Also
change the name of the function to something to remember (not forgetting to
replace the name as the start of the second line). It may well be worth
adding some checking code to make sure the value you pass is not null or out
of the bounds.

Then in the query you can just use your newly created mask function.

 

[Peter R. Fletcher]

In addition to what others have said, note that using Excel functions
in Access is enormously slower than either using native Access
functions or coding the calculations in VBA. This may not matter for a
complex 1-off calculation, but using an Excell function within a Query
is likely to be unworkable.

You are right about the nature of this question.

I just thought that since MS Excel offers PMT and FV functions that do
something similar, I somehow felt that there will be an Access variant of
these functions that can be easily adopted to a database model.
I have two options here :
--- 1) I've got a formula : EMI = P×r×(1 + r)n/((1 + r)n - 1), which I can
tweak to suit my db structure -
--- 2) or else - simply use the Excel functions (with enough tweaking, again).
Just looking for a probable better solution.

Thank you very much for the reply.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Guest]

Fortunately, "PMT" and "FV" functions do exist in Access VBA. What I meant
was I needed to modify the inputs and / or the results of these functions to
get what I want. And I hope I figured that out myself.

( For the past two days, I've been googling extensively for Ready-to-Use
Code or still better, a sample db. Finding none, my conclusion is that :
there aren't many Accounting Apps with Access.)