4

My Ex owes me $66000 and I need to give him a payment plan with 8% interest over 5 years.

The monthly repayments need to tier roughly as follows (he's going to earn more in the 2nd and 3rd years and then less)

I calculated something like this:

  • Year 1 first 6 months at $700 pmth = 4200
  • Year 1 2nd 6 months at $1000 pmth = 6000
  • Yr 2 at $1800 pmth = $21,600
  • Yr 3 at $1800 = $21,600
  • Yr 4 at $700 = $8400
  • Yr 5 at $350 = $4200

I have a good chance he'll repay because this way the two years he earns the most he pays back the most. Now how do I charge 8% interest compounded?

If I asked for $1100 per month for 5 years then there is more to lose Years 4 & 5 when he's earning less again.

  • This is terrific. I really struggled with calculating the interest rate but I'll go create the spreadsheet. Yes I can see I'll need to change the payment amounts because of the added interest. This is a great start. I love creating spreadsheets LOL – L Williams Feb 13 at 4:22
  • 8% effective interest rate or nominal interest rate? – mastov Feb 13 at 14:29
  • I don't know the difference. – L Williams Feb 14 at 17:48
  • The nominal rate is simply divided by 12 to get the monthly rate. However, because of compounding (the interest of the first months generates interest during the rest of the year), over the whole year you will have paid slightly more than 12 times the monthly interest rate. A nominal interest rate of 8% compounded monthly will yield more interest in $ over the year than 8% applied once per year. The effective rate takes that into account. There the monthly rate is calculated in a way that yields the same interest in $ over the year as applying the interest rate only once per year. – mastov Feb 15 at 11:24
2

At the end of each month, subtract the previous month's principal payment, and add 1/150 of the current principal as a separate interest payment. (1/150 = 8/100 * 1/12, or the annual percentage rate divided by 12 to get a monthly interest rate.)

Month         Principal     Principal payment     Interest     Total payment
-----------------------------------------------------------------------------
  1           66000                700              440.00        1140.00
  2           65300                700              435.33        1135.33
  3           64600                700              430.67        1130.67
  4           63900                700              426.00        1126.00      
  5           63200                700              421.33        1121.33
  6           62500                700              416.67        1116.67
  7           61800               1000              412.00        1412.00
  8           60800               1000              405.33        1405.33
  9           59800               1000              398.67        1398.67
 10           58800               1000              392.00        1392.00
 11           57800               1000              385.33        1385.33
 12           56800               1000              378.67        1378.67   
 etc

In this case, I'm not amortizing the loan so that the principal portion, not the total payment, is fixed. As a result, the total payments are higher each month.

If you need to cap the total payment for each month as in your original question, you need to extend the life of the loan, since the principal is being paid down much more slowly.

Month         Principal     Principal payment     Interest     Total payment
-----------------------------------------------------------------------------
  1           66000                260              440.00         700
  2           65740                261.74           438.26         700
  3           65478.26             263.48           436.52         700
  4           65214.78
  ...
 60           15523                246.51           103.49         350

Because these payments now include principal and interest, the principal won't reach 0 by the end of year 5, so you need to either increase the monthly payments, or extend the life of the loan by several years.

(However, I do note that by adding $200 a month to each of your proposed payments, the loan is paid off in 59 months, with a smaller partial payment in the last month.)

If you aren't comfortable drawing up tables like this yourself using a spreadsheet, I recommend hiring an accountant draw them up for you.

  • This is another good way to do it. However, both the OP and her ex-spouse may find it confusing to have every single payment be a different amount. – Ben Miller - Reinstate Monica Feb 12 at 15:41
  • Right, but the OP's original scheme is an amortization table for a 0% interest; the table completely changes if you change the interest rate. For the table in the question, you can treat those as containing interest payments, but then you won't fully pay off the principal by the end of year 5. – chepner Feb 12 at 15:45
  • Agreed, the payments laid out from the OP don't cover any interest, so in order to pay off the note in 5 years, the payments need to be higher. I think everyone's answer agrees on that. – Ben Miller - Reinstate Monica Feb 12 at 15:48
  • Yeah, I don't think I'm providing any new information in this answer, just being more explicit in showing the tables. – chepner Feb 12 at 15:56
  • On the contrary, your approach is valid, but different than the other two answers. – Ben Miller - Reinstate Monica Feb 12 at 21:24
2

Let's assume you mean 8% nominal annual interest, compounded monthly.

With

i = nominal annual interest, compounded monthly
r = monthly interest
s = principal
d = payment
n = number of months

i = 0.08
r = i/12 = 0.00666667
s = 66000
n = 5*12 = 60

d = r (1 + 1/((1 + r)^n - 1)) s = 1338.24

If the payments were to be equal, a monthly payment of $1338.24 would repay the loan.

A formula for the balance at any month is

balance[m] = (d + (1 + r)^m (r s - d))/r

balance[0] = 66000
balance[1] = 65101.76
...
balance[n] = 0

enter image description here

With varying payments

Year 1 first 6 months $700
Year 1 2nd 6 months  $1000
Year 2               $1800
Year 3               $1800
Year 4                $700
Year 5                $350

Resetting d and setting s as the new balance

r = 0.00666667
s = 66000

d = 700
m = 6
s = balance[m] = 64413.77

d = 1000
m = 6
s = balance[m] = 60932.75

d = 1800
m = 24
s = balance[m] = 24787.55

d = 700;
m = 12;
s = balance[m] = 18129.95

d = 350;
m = 12;
s = balance[m] = 15277.26

So this payment scheme, with 8% interest, would leave a balance of $15,277.26

Scaling all payments by x = 1.18395 results in a zero balance.

I have also added a running total for the accumulated interest a.

r = 0.00666667
s = 66000
a = 0

d = 700 x = 828.76
m = 6
start = s
s = balance[m] = 63628.20
a = a + m d - (start - s) = 2600.78

d = 1000 x = 1183.95
m = 6
start = s
s = balance[m] = 58992.99
a = a + m d - (start - s) = 5069.25

d = 1800 x = 2131.10
m = 24
start = s
s = balance[m] = 13925.84
a = a + m d - (start - s) = 11148.60

d = 700 x = 828.76
m = 12
start = s
s = balance[m] = 4763.64
a = a + m d - (start - s) = 11931.55

d = 350 x = 414.38
m = 12
start = s
s = balance[m] = 0
a = a + m d - (start - s) = 12140.49

So the adjusted payment scheme is

Year 1 first 6 months $828.76
Year 1 2nd 6 months  $1183.95
Year 2               $2131.10
Year 3               $2131.10
Year 4                $828.76
Year 5                $414.38

and the total interest paid is $12,140.49

This calculation assumes payments happen at the end of every month, as is normal for a loan. I.e. the principal is loaned, then one month later the first repayment is made. The last payment occurs at the end of the last month of the term of the loan.

0

I would suggest creating a spreadsheet with the following columns: Current Principal, Monthly Interest, Principal Payment, Total Monthly Payment, Total Interest To Date. Fill the first row with 66000, =(Current Principal*.08/12), =(Total Monthly Payment) - (Monthly Interest), 700, (Monthly Interest) respectively. Then, for the rest of Current Principal, calculate the Current Principal of each row as Current Principal of the previous row minus Principal Payment of the previous row. For the rest of Total Interest To Date, calculate each row as Total Interest To Date of the previous row plus Monthly Interest of the current row. For Monthly Interest and Principal Payment, copy the respective formulas from the first row.

Now you can just fill in the Monthly Payment column with whatever numbers you like, as the spreadsheet program will calculate all the other columns automatically.

  • Wow .. I knew there had to be an easy way using Excel. Thank you so much! I'll go create the spreadsheet and come back if I have glitches. – L Williams Feb 13 at 4:15
  • I did it! Thanks for your help! I adjusted the payment amounts so it could be paid off in 60 months. The payments were $500 for 6 months then bumped up to $1200, $1800, down to $1200 and one last payment of $352.59 to have it paid off in the 60th month. I created a spreadsheet but I don't know how to copy to here. The last column was the payment minus the interest, then I took that cell amount off the principal to create the new principal balance. Thanks again for all your help!!! – L Williams Feb 16 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.