Hello All,
I am using Excel 2010.
I have a formatting question. The following is what I need to do in Excel (for only one (1) customer):
1. Keep track of credit line payments;
2. Keep track of credit line charges;
3. Keep track of how many months between the credit line charge and the payment;
4. Keep track of late fees, interest, interest rate, interest rate changes, balance of credit line.
Here is my problem:
We have set up a quasi-trust for a member of our family. It starts with $3000 and our relative can access the monies by requesting an amount from the trustees (wife and I are trustees).
Relative must start to pay back the monies borrowed as follows:
1. monthly payments;
2. minimum payment = amount borrowed / 12 + 8% simple interest;
3. no interest if payment in full is received within 1 month;
4. if payment is more than 1 month late, a penalty will be applied in the amount of $35;
5. trustees keep 1% of trust balance per month for a managing fee (minimum = $10/monthly);
6. if relative makes 4 consecutive on-time payments or the equivalent of four (4) minimum payments, the annual interest rate will be reduced by 1 percentage point per annum;
7. at no time will the interest rate go below 1 percent per annum;
8. if relative fails to timely pay back borrowed amount(s) according to the minimum amount for three (3) consecutive months, the interest rate applied to the paying back of the monies loaned will be increased by 0.5 percentage point per annum;
9. at no time will the interest rate per annum charged exceed 12 percent;
8. all amounts collected are put back in the trust for the benefit of our relative until the amount left is $0.00 or when we decide to stop loaning money.
Question:
How would you set this up in an Excel spreadsheet?
If you can think of ways of accomplishing this that are simpler than using Excel or simpler than what I have written, please feel free to comment.
Very confused and thankful for any assistance,
edsager
I am using Excel 2010.
I have a formatting question. The following is what I need to do in Excel (for only one (1) customer):
1. Keep track of credit line payments;
2. Keep track of credit line charges;
3. Keep track of how many months between the credit line charge and the payment;
4. Keep track of late fees, interest, interest rate, interest rate changes, balance of credit line.
Here is my problem:
We have set up a quasi-trust for a member of our family. It starts with $3000 and our relative can access the monies by requesting an amount from the trustees (wife and I are trustees).
Relative must start to pay back the monies borrowed as follows:
1. monthly payments;
2. minimum payment = amount borrowed / 12 + 8% simple interest;
3. no interest if payment in full is received within 1 month;
4. if payment is more than 1 month late, a penalty will be applied in the amount of $35;
5. trustees keep 1% of trust balance per month for a managing fee (minimum = $10/monthly);
6. if relative makes 4 consecutive on-time payments or the equivalent of four (4) minimum payments, the annual interest rate will be reduced by 1 percentage point per annum;
7. at no time will the interest rate go below 1 percent per annum;
8. if relative fails to timely pay back borrowed amount(s) according to the minimum amount for three (3) consecutive months, the interest rate applied to the paying back of the monies loaned will be increased by 0.5 percentage point per annum;
9. at no time will the interest rate per annum charged exceed 12 percent;
8. all amounts collected are put back in the trust for the benefit of our relative until the amount left is $0.00 or when we decide to stop loaning money.
Question:
How would you set this up in an Excel spreadsheet?
If you can think of ways of accomplishing this that are simpler than using Excel or simpler than what I have written, please feel free to comment.
Very confused and thankful for any assistance,
edsager