SQL Mini financial application with a web interface(repost)

This is a request for a small financial applications with reporting and printing capabilities for managing a collection of loans, borrowers, investors, and associated payments.

1. A loan is associated with a property, granted to a borrower

2. A client invests in one or more properties

3. borrowers make payments, which in turn is disbursed amongst inverstors

4. After deducting our fees, payments are disbursed amongst investors.

5. This is an interest payment loan system - in other words, unlike a typical mortgage, they pay the interest for the duration of the loan, and then make a balloon payment for the loan amount at the end.

Please see attached document for more detail.

I am open to MySql as much as I am to SQL Server - to Perl as much as to PHP as much as to C# or Java! And I am open to Linux as much as I am to Windows.

The reporting feature, would be easier using SQL Server with Chrystal reports embedded e.g. Visual Studio. HOWEVER, the reporting and printing should be a simple listing of available reports, probably 2-3 ... After that, based on your documentation, I will develop the additional reports and add to the list ... By reporting, I am not asking you to develop a general purpose database reporting package! I just need a couple of reports and see I can add more, and I will add them myself ... I should be able to print them or store them in a spreadsheet to have it print them.

Let's get started ...

Thanks,

Bahram

## Deliverables

# The predictable behavior of the system.

Imagine the following situation where a borrower, someone who needs a loan, instead of going to a bank, comes to us to borrow money against his property/ies for a period of N months. After doing our due diligence, and making sure he is able to pay us back, we qualify him and we give him the loan.

1. There has to be a property/ies to secure the loan - the property/ies will be the collateral.

2. We only are interested in collecting interest on this loan ? in other words, unlike a typical mortgage where both interest and principle are being paid, we only want interest. It is as if we rent the borrower the loan amount, and he pays the rent e.g. the interest, for that loan for N months, and at the end of the N months, he gives all the money back!

3. As we are giving the borrower this money, we also look for investors, folks in our clients list, to help us put the actual loan amount together and pay the borrower the loan he needs. These investors become partners in the interest that we collect from the borrower every month.

4. Typically, we are considered the first investor in a loan at 100%. As we identify clients to invest in that loan, our stake reduces and is replaced by the investors' money. The collected interest from the borrower is divided between investors based on their investment in that loan.

5. After N months, the borrower writes a check for the loan amount that he originally borrowed, or whatever the calculated current balance is, the money is then divided between investors to give them back the money they had invested in this loan, and the account closes.

In simpler terms, and more specifically, Joe comes to us to borrow $100,000 on his two properties. After studying the situation, we decide to give him the $100,000 at 8% interest rate, compounded daily. In other words, for every day he has the $100,000 we collect 8% from him. The compounding formula is

P = C(1 + r/365) t where C is the capital, r is the interest, and t is the amount of time.

P = 100000(1+[url removed, login to view])^30 = $100659.62 in other words $100,000 collects $659.62 in one month e.g. 30 days. For the entire year, the sum will be $108327.75 which is $8,327.75 worth of interest.

In the meantime, we identify Mary who wants to invest her $10,000 e.g. 10%, in this loan. The catch however is that we notify her in the contract that her interest is only at 5% ? in other words, although we collect 8% interest from Joe, we offer Mary on 5% interest which is substantially better than the bank, less than what we collect ourselves. The 3% different is our fees for having found Joe, for having managed the payments that come from him, and in fact for the risk we took initially granting him the $100,000 in the first place. The 5% is perfectly welcomed by Mary. Therefore every month, Mary receives an interest of 5% for her $10,000, for which we collected 8% from Joe. In other words, we collect $659.62 from Joe after 30 days. If Mary's $10,000 has been in for those 30 days, she should get: $10,000(1+5%/365)^30 which adds up to $10041.17 or an interest of $41.17 for Mary. Remember, collected interest = P - C

Let's make sure we understand this: We find investors for the loan above to make up 100% of the loan amount. In other words, we no longer have any stake in that loan whatsoever except for managing it. At the end of the month, we collect $659.62 in interest owed, based on the original agreement with Joe who is paying us an interest of 8%. We pay the $100,000(1+5% / 365)^30 - $100,000 = $100411.17 - $100,000 = $411.17 to the invested clients. The difference e.g. $659.62 - $411.17 = $248.45 is the fees the we keep for ourselves because we are providing this service to the investors. Keep in mind that if we remain an investor in the loan, we also collect the same kind of 5% interest like everyone else with a difference explained later a Guaranteed and Non-Guaranteed investor.

This application is about managing these borrowers and their properties, the loans we issue, the investors who put their moneys to fund such loans, the payments that we receive from the borrowers, and the interest payments that we make to the investors, like the $41.17 that we have to issue to Mary above. The system is responsible for managing these payments, and for printing cheques to be sent out to the respective parties. WE DO NOT NEED TO HAVE A FINANCIAL GATEWAY to any banks or other financial institutes. I will however require for a couple of sets of transactions to be stored in a spreadsheet to be manually sent to banks.

The requirements in summary:

1. Managing clients, borrowers, loans, properties, IBA, and all the relevant payments

2. Printing CHEQUES to send cheques back and forth to respective parties

3. Saving certain reports as spreadsheet to be eventually sent to banks

4. Printing reports ? If you show me (or explain the code snippet) for where we can make and print 1-2 reports, I can create a lot of other reports on my own ? I just need to make sure we have a way of seeing the list of reports and invoking them.

# Guaranteed and non-Guaranteed clients

When we identify clients e.g. investors, for a loan, they may choose to invest either as Guaranteed or non-Guaranteed. As the name implies, regardless of what the borrower does, the ***guaranteed investors*** get paid the interest that the company owes them like clockwork - NO MATTER WHAT HAPPENS. The payment date for collecting the borrower's interest payment is the day we issued the note, e.g. we gave him the money. If Joe in the example above gets his loan funded on the 18th, that is the day we expect to receive his monthly payments give or take a careful grace period, and that is the day we expect to write checks for all the guaranteed investors for this loan. If Mary invests in Joe's loan, she gets paid the interest that the company owes her on the 18th of every month. The only exception is the first month because in all likelihood, she did not write a $10,000 for her investment in Joe's loan, also on the 18th !!!

**Example**: If Mary gives us her money on the April 28th, she collects money from the 28th April, 29th, 30th, 1st, ? , 17th May. That is for 20 days. e.g. April is a 30 day month. $10,000 (1 + 5%/365)^20 = $10027.43 or $27.43 for her first 20 days of investment in this loan. Next month on June 18th, she gets a check for 31 days of her money having been invested or $10,000 (1 + 5% / 365) ^31 = $10042.55 or $42.55 for her share of the interest.

If an investor chooses, she can be a ***non-guaranteed investor*** in a loan. The non-guaranteed investors are paid only when the borrower makes a payment. Although by definition the interest paid to the guaranteed investors is different than the interest paid to non-guaranteed investors, they tend to closely match, if not practically the same. The investors who choose to be non-guaranteed on a particular loan, benefit from the "penalties" that a loan may incur due to late payment of other reasons, the percentage of which goes to non-guaranteed investors.

# Capital and Financial Services

There are two financial entities behind the scenes managing these funds: Capital and Financial Services. Capital is responsible for providing all the funds that anyone and everyone needs. Financial Services receives checks and sends checks to the respective parties.

Example: When a cheque comes from a borrower for a particular loan, the borrower writes the cheque to Financial Services. The amount that needs to be given to the NON-Guaranteed clients is calculated and individual cheques for them are issued - these checques are from Financial Services to different Non-Guaranteed investors. The rest is sent to Capital also as a cheque from Financial Services to Capital.

Capital is responsible for funding everything Financial Services needs! Everyday, the system checks to see if there are any guaranteed payments due e.g. is a loan date today! If the case, the total amount of interest to the guaranteed investors is calculated and a cheque is issued from Capital to Financial Services. Financial Services in turn breaks up the cheque into subsequent individual cheques to guaranteed investors, issues those checques and sends them to the respective investors.

The Financial Services is expected to maintain a total balance of $100 for ever. All the funds coming to it, should leave immediately and disbursed either to Capital or to respective investors as described above.

Capital is the first 100% non-guaranteed investor in every loan.

# Investing in a loan

Once a loan is approved, Capital funds Financial Services and Financial Services sends the funds to the borrower. In reality, this is the only part of the system that we do show the transactions but do not actually issue any checques and it happens through wire transfers from Capital to Financial Services and from Financial Services to the Escrow company. It is a highly infrequent activity and manual processing of this step and going through the wire transfer is not an issue at this point. The system needs to show that the transfer had taken place.

Once done, clearly, Capital will be the sole investor in this loan at 100%. On one hand as an organization, we collect the 8% I mentioned above from the borrower. On the other hand, we give Capital 5% for its share of the collected interest when it arrives. Again, remember, Capital is NON-Guaranteed investor in a loan.

Once a investor is identified, the investor writes a cheque to Financial Services for her investment - Financial Services immediately reissues a cheque from Financial Services to Capital and passes the funds to Capital to replace what it had funded when the loan was issued. The computations for who collects how much interest is as I described above.

Eventually, 100% of the loan is out of Capital and with different investors, some of which may be guaranteed and others non-guaranteed. In other words a loan is comprised of a number of heterogeneous investors that are guaranteed and non-guaranteed.

# Early Withdrawal

What happens when an investor, Guaranteed or non-guaranteed decides to withdraw early? She signed up to invest, now she needs her money and is pulling her money out. In such cases, we find a replacement to take her place. Does that sound familiar? Remember the Capital was 100% invested in the loan at the start. In fact, Capital is client #1 for all these loans. Then other clients come in and invest in the same loan, which basically seems as if they are purchasing Capital's shares! We do the same with the early withdrawals and we have so far never had a problem finding another investor to take the place of a withdrawing investor.

Here is how it goes: Mary, needs her money and needs to withdraw all or a portion of her money. We find Samir, who is another client interested in taking her place. Samir writes Financial Services a cheque for $10,000. When the cheque is received, the penalty for early withdrawal is deducted, say $900, and right there a cheque is written to Mary for $9100, which is basically from the $10000 that we just received from Samir. Behind the scenes, we transfer the $10,000 investment that Mary had to Samir ? in other words, Samir is now the owner of 10% of that $100,000 loan, under the same terms and conditions. In fact we do the same thing when we move investments from Capital at the start, when investors come in. Initially, Capital was 100% invested in this $100,000 loan when we started. When Mary decided to invest, she wrote a cheque for the $10,000 to Financial Services. Financial Services took the cheque and wrote a cheque to Capital for that $10,000 ? in this case, there is of course no penalty, as there may not be penalty for Mary e.g. $0, for early withdrawal if so was negotiated. As for the case of the early withdrawal, $9100 is written to Mary and the remaining $900 is written as cheque to Capital.

Please read the attached documents carefully before you bid on this project.

Thank you.

* * *This broadcast message was sent to all bidders on Friday May 20, 2011 7:26:32 PM:

Hello, I have a few questions asking about the REPORTS requirements and operating environment. 1. I am open to MySql as much as I am to SQL Server - to Perl as much as to PHP as much as to C# or Java! And I am open to Linux as much as I am to Windows. 2. The reporting feature, would be easier using SQL Server with Chrystal reports embedded e.g. Visual Studio. HOWEVER, the reporting and printing should be a simple listing of available reports, probably 2-3 ... After that, based on your documentation, I will develop the additional reports and add to the list ... By reporting, I am not asking you to develop a general purpose database reporting package! I just need a couple of reports and see I can add more, and I will add them myself ... I should be able to print them or store them in a spreadsheet to have it print them.

PHP

หมายเลขโปรเจค: #3326857

เกี่ยวกับโปรเจกต์