Calculate A Payout Figure

[Guest]

Hi guys,

I have a simple calculation in Excel that i would like to re-create in
Access somehow. There's a snipit of the excel spreadsheet below.

Payment Month Loan Interest Loan+Int Repaymen Payout Figure
1 Jan $15,002.00 $91.14 $15,093.14 $359.08 $14,734.06
2 Feb $14,734.06 $89.51 $14,823.57 $359.08 $14,464.49
3 Mar $14,464.49 $87.87 $14,552.36 $359.08 $14,193.28
4 Apr $14,193.28 $86.22 $14,279.50 $359.08 $13,920.42
and so on...

The interest rate used to calculate above is 7.29% (0.0729)

I have not created any tables yet, i'm utterly confused how to approach this.

I'd like to display the above in a subform in datasheet view.

Can anyone give me some ideas on how i should approach this.

GregInOz

 

[Steve Schapel]

Greg,

Looks to me kike the table design needs to be something like this...

Table: Loan
LoadID
BorrowerID
InitialLoanAmount
InterestRate

Table: Repayments
RepaymentID
LoanID
RepaymentDate
RepaymentAmount

If the interest rate can vary during the course of the loan, or if
supplementary credits can be drawn down, or any other complicating
factors, then these will have to be catered to.

In the sample data you provided, the initial loan amount, and the
repayments, are the only "real" data - the rest of the figures are all
derived. You could get a similar output in Access using a query based
on the above tables, and hence shown on a form or report.

But, here's what I am wondering... why do you want to do this in Access?
Blasphemous, I know, and I'm in big trouble if Jeff Boyce sees this.
But on the face of what you have showed so far, this looks like
spreadsheet territory.

 

[Guest]

Hi Steve,

We don't have to worry about varying interest or supplementary credits,
thank goodness.

I have a database for a fleet of rental vehicles and wanted to add this
feature. Currently i paste the excel spreadsheet into a report, but this is
cumbersome and not user friendly and i'm the only one who can do this and of
course i won't always be there.

If the user only has to type in a few basic answers into a form and results
display below in a subform or report, then surely this is a more user
friendly approach.

I invisage the following.

Say a lease is over 48 months we would see a subform below on a main form
with 48 entries indicating each payment, similar to the sample on my previous
post. On the main form a field called say "Payout" this would display the
current balance of the loan as at the current date.

I'll set up the tables tonight and see how we go. Rather than create two
new tables, i guess it would make sense to use the table i already have
"tblCars" with primary key "FleetNo" which is/must be a text field. I would
like the subform to appear on the main form "frmCars".

Should we have a field called "Term" e.g Term: 48

Would we use Code to create 48 into the subform OR Repayments table.

Regards,

GregInOZ

 

[Steve Schapel]

Greg,

Do you mean you want to enter all 48 records from the git-go, or only
enter them as there is a payment made?

 

[Guest]

Steve,

I think from the git-go would be better. I was thinking of having a field
above the subform showing the current balance as at the current date.

Greg.

 

[Steve Schapel]

Greg,

Doesn't that sort of assume that all payments will always be made on
time? Or else you will need both a 'Payments Due' and a 'Payments
Received' process?

 

[Guest]

Steve,

Yep, the assumption is correct.

The payments are automatically made on the same day everymonth or next
working day, but i'm not concerned about being out by 1-2 days on the date.
I'm not even concerned if a payment was missed as it would be caught up
within 1 week anyway.

I'm trying to make it automated as possible as there is over 200 vehicles
and would be too labour intesive for someone to keep upto date.

I guess the subform could be referred to as a Repayment Schedule.

Greg.