A borrower calls in to ask how much it would take to pay off their loan in full. The user handling the call types in a loan number and my spreadsheet calculates the dollar amount required to pay off the loan. At least that is the goal of my ongoing project. Pray for me as I attempt to create the most complex spread sheet I've ever worked on with my intermediate excel skills (I score myself a 6.1 out of 10 in excel skills).
Consider me an extreme beginer at VBA although in 1982 I taught myself to program in Basic on the Commodore 64. That was the last time I used basic. I'm wondering if this requires VBA or if I can make a gigantic 'IF' statement.
There are six types of distinct events (usually unrelated to each other) that occur on in the subject column (date column) of my workbook as follows;
I work exclusively on loans that are in default, which is why some of the above might seem unusual. But don't frown upon me because I'm not a debt collector that harrasses folks. Instead I simply provide analysis and numbers to the manager to help the borrower out of debt and hopefully the manager forgives some or much of the debt.
The riddle I'm trying to solve is that this column needs to fill with dates in chronological (sequential) order. The goal is to show the sequence of events in one column with the earliest date first and the most recent date at the bottom. Various other pieces of data in other cells or sheets are used to decide which date belongs on the next row down for up to 60 rows of dates. Often times less than 20 rows end up being required. This date row will be used to calculate how much interest has accrued from date to date to date.
I would very much appreciate your suggestions, advice, ideas, questions, anything from experts is awesome.
Kind Thanks,
Eddie
Consider me an extreme beginer at VBA although in 1982 I taught myself to program in Basic on the Commodore 64. That was the last time I used basic. I'm wondering if this requires VBA or if I can make a gigantic 'IF' statement.
There are six types of distinct events (usually unrelated to each other) that occur on in the subject column (date column) of my workbook as follows;
- UPB Delta = These are the various dates on which the loan balance might have changed (Unpaid Principal Balance). This event can not happen more than 5 times on this workbook. These events are almost always the first few dates in this column, followed by the other dates.
- Pmt Recv'd = each and every time that we receive a payment from the borrower. (can happen from zero to up to 60 times).
- Maturity = This is the date on which the loan matures and thus is payable in full. This date only occurs once and sometimes not at all on this column.
- Due Dates = this is on the same day of the month every month representing the date on which regular monthly payments are due
- Rate delta = this is the date on which the interest rate changes if it's a variable rate loan. Can happen from zero to many times
- defalut date = this is the date on which the loan went bad because the borrower was too late on the payment due...or didn't pay at all (as in they consider themselves debt free
)
I work exclusively on loans that are in default, which is why some of the above might seem unusual. But don't frown upon me because I'm not a debt collector that harrasses folks. Instead I simply provide analysis and numbers to the manager to help the borrower out of debt and hopefully the manager forgives some or much of the debt.
The riddle I'm trying to solve is that this column needs to fill with dates in chronological (sequential) order. The goal is to show the sequence of events in one column with the earliest date first and the most recent date at the bottom. Various other pieces of data in other cells or sheets are used to decide which date belongs on the next row down for up to 60 rows of dates. Often times less than 20 rows end up being required. This date row will be used to calculate how much interest has accrued from date to date to date.
I would very much appreciate your suggestions, advice, ideas, questions, anything from experts is awesome.
Kind Thanks,
Eddie
