Cash Flow Analysis

[cbh35711]

I have been tasked with translating an Excel Cash Flow(NPV) calculator to Access. Before just wandering off in my own direction, I was curious on if someone had an example of a NPV calculator they have done/seen in Access?

Any assistance would be appreciated.

Thanks,



Chris

[jzwp11]

I have not done one myself, but after a quick search, I found this site.

[cbh35711]

I am aware of the function, which I would totally use. But I'm more interested in the structure of the underlying database. Just looking for some ideas.

Thanks!

Chris

[jzwp11]

The table structure is a totally different issue compared to just a calculated NPV. With Excel, you have to be careful because spreadsheets do not typically follow a normalized structure whereas a relational database must. What structure is appropriate depends on your business process and with what raw data you are dealing. Could you please provide more details with respect to those two items?

[cbh35711]

I understand how NPV works, but in creating it for Access I need to make sure i'm using the best(ish) DB structure. This will be 100% under input.

Should I create records at the loan level(having tons of columns for the payments) or at the payment level(one record per payment). I guess that's where i'm stuck. I would think the cleanest way would be to have one record for each payment. Looks like either way if i am to use the NPV function, the payments will have to be in an array.

Thoughts?

Thanks!

Chris

[jzwp11]

If a loan has many related payments, that describes a one-to-many relationship which by normalization rules requires the payments to be records in a related table.

[cbh35711]

Oh yes, in a related table but in that table, should i have 24 records(assuming 24 payments) for that loan or one row with 24 additional(payments) elements. Sounds like the 24 records.

Do you agree?

Thanks!

Chris

[jzwp11]

records is the way to handle it.

[cbh35711]

Ok thanks jzwp11.

I am still interested in looking at any Access examples to calculate NPV. I have not been able to find Access examples by Googleing.

Thanks!

Chris

[jzwp11]

The NPV() function is a built-in function in Access and VBA. I'm not sure how you would supply the values necessary if just using the NPV() function in a form or query in Access. In VBA code, you can work with arrays with no problem. You would have to supply the rate and the values (representing the initial amount and payments). You would have to pull the information from your loan and payments table and populate those in an array that is then referenced by the NPV() function.

[ssanfu]

Ok thanks jzwp11.

I am still interested in looking at any Access examples to calculate NPV. I have not been able to find Access examples by Googleing.

Thanks!

Chris

What jzwp11 is saying is NPV is a calculation - you can roll your own or use the built in function. But it is just a calculation.
You need to determine your table structure first and foremost.

I would think you need a minimum of 3 tables to start with:
a table for originator data linked one-2-many with a table of loan data.
And the table of loan data linked one-2-many with a table of payments.

The originator table could be linked to a table for OriginatorNameTypes
the Originator names;
the Originator types = individual/ couple/ partnership/ corporation

Each originator would be a separate record
Each loan would be a separate record
Each payment would be a separate record.

Since you know your requirements and we don't, design a table structure and post back with any questions. We will be glad to offer suggestions.

[jzwp11]

Along what Steve says, if we had a structure along with some data, we can help you with the getting the coding and function to work together to get the NPV calculation.

[cbh35711]

Thanks for the input guys!

I'll work on it

Chris

[cbh35711]

Attached is a zip containing the excel document I want to emulate and a wire frame database. CashFlow.zip

Not included in this database are the Loan And Borrower tables. Loan is linked by Acct_reference_id.

So my thinking is that the user will input parameters and click a button to create the rest of the cash flows. This would only be possible for Stardard payments. If the loan is irregular, then they user will have to input all values.

Ideally, the user would be able to review and manipulate the calculated futures in much the same way the user would manually input irregular future cashflows.

This may be jumping ahead, but i know an issue will be allowing the user to input multiple records at the same time. I've googled some, but still unsure if this may be a hindrance.

Thanks!

Chris

[cbh35711]

So i embedded the excel file in the access database. It seems to be the easiest way to input the data. So now i just have to figure out reading the excel file back into the database.

Thanks