Unanswered: Creating multiple amoritization tables, and summarizing
Hello all! I hope someone here can help me.
I am a very active user of a peer-to-peer lending site, and have funded over 700 loans over the past few years. The problem I have is that I have no way to accurately predict the payments I will receive as these loans are repaid.
What I want to do is import a table from excel that contains the information in each loan. The fields would be as follows:
Term (number of payments)
Purchase (amount I paid for the loan)
Payment Amount (monthly payment I get)
(The following will be filled in as the loan is fully paid)
Profit (Total paid - purchase)
Profit Percent (profit/purchase)
From that table, I would like access to generate a sub-table, with the following:
-Loan ID (same as first table)
-Payment date (Issue date + 1 month for x number of months (term of loan))
-Payment amount (will equal payment amount in first table)
From there, I should be able to do a group-by query or report that shows the total payments for each month, not including the loans that have a pay-off date entered.
I found an excel template that will generate the list of payments, but I would have to run it manually on all 700 loans. There has to be a better way, right?
I really appreciate any & all advice I can get. My excel spreadsheet has worked out well enough, but this is getting too big to track easily.