 |
| ||
| |||
|
| Math Central | Quandaries & Queries |
|
How would you enter a formula in Excel for the following: payment is applied to principal balance, then 8% interest compounded daily, then .25% monthly penalty |
Hi Ema,
I need some clarification. What I see is that you owe some money. You make a payment and then 8% per year, compounded daily, is added to what you owe until the next payment. How long is that? The 0.25%
monthly penalty is applied to what amount? You didn't say what you wanted the Excel program to produce.
Penny
Ema wrote back:
Hi Penny. I owe $16K. I pay $300 a month. They apply the payment to the principal. They charge 8% APR compounded on the daily balance and .25% penalty on the monthly balance. I'm trying to create an Excel chart so I know how many months it's going to take to pay this off. Thank you so much for your assistance.
Hi Ema,
Suppose that $P is the amount you owe at the beginning of the month. The annual interest rate is 8% so to obtain the daily interest rate you divide by 365. Thus the amount you owe d days later is
$P(1 + 0.08/365)d
When d is 30 days (I assume they are using a 30 day month) a 0.25% penalty is added to make your debt at that time
$P(1 + 0.08/365)30
(1.0025)
You then make a $300 payment so the amount owing at the beginning of the next month is
$P(1 + 0.08/365)30
I hope this helps,
Penny
Hi Ema. Here is a spreadsheet showing you the declining balance and the accumulated payments over time. It appears it will take 74 months to pay it off (6 years, 2 months).
| At the end of month number | You have paid a total of: | Your loan balance is |
| $16,000.00 | ||
| 1 | $300.00 | $15,847.21 |
| 2 | $600.00 | $15,693.00 |
| 3 | $900.00 | $15,537.39 |
| 4 | $1,200.00 | $15,380.33 |
| 5 | $1,500.00 | $15,221.84 |
| 6 | $1,800.00 | $15,061.88 |
| 7 | $2,100.00 | $14,900.46 |
| 8 | $2,400.00 | $14,737.55 |
| 9 | $2,700.00 | $14,573.14 |
| 10 | $3,000.00 | $14,407.21 |
| 11 | $3,300.00 | $14,239.77 |
| 12 | $3,600.00 | $14,070.78 |
| 13 | $3,900.00 | $13,900.23 |
| 14 | $4,200.00 | $13,728.12 |
| 15 | $4,500.00 | $13,554.42 |
| 16 | $4,800.00 | $13,379.13 |
| 17 | $5,100.00 | $13,202.22 |
| 18 | $5,400.00 | $13,023.68 |
| 19 | $5,700.00 | $12,843.51 |
| 20 | $6,000.00 | $12,661.67 |
| 21 | $6,300.00 | $12,478.16 |
| 22 | $6,600.00 | $12,292.96 |
| 23 | $6,900.00 | $12,106.06 |
| 24 | $7,200.00 | $11,917.44 |
| 25 | $7,500.00 | $11,727.09 |
| 26 | $7,800.00 | $11,534.98 |
| 27 | $8,100.00 | $11,341.11 |
| 28 | $8,400.00 | $11,145.45 |
| 29 | $8,700.00 | $10,947.99 |
| 30 | $9,000.00 | $10,748.71 |
| 31 | $9,300.00 | $10,547.61 |
| 32 | $9,600.00 | $10,344.65 |
| 33 | $9,900.00 | $10,139.82 |
| 34 | $10,200.00 | $9,933.11 |
| 35 | $10,500.00 | $9,724.50 |
| 36 | $10,800.00 | $9,513.97 |
| 37 | $11,100.00 | $9,301.50 |
| 38 | $11,400.00 | $9,087.08 |
| 39 | $11,700.00 | $8,870.68 |
| 40 | $12,000.00 | $8,652.29 |
| 41 | $12,300.00 | $8,431.90 |
| 42 | $12,600.00 | $8,209.47 |
| 43 | $12,900.00 | $7,985.00 |
| 44 | $13,200.00 | $7,758.47 |
| 45 | $13,500.00 | $7,529.85 |
| 46 | $13,800.00 | $7,299.12 |
| 47 | $14,100.00 | $7,066.28 |
| 48 | $14,400.00 | $6,831.29 |
| 49 | $14,700.00 | $6,594.14 |
| 50 | $15,000.00 | $6,354.81 |
| 51 | $15,300.00 | $6,113.27 |
| 52 | $15,600.00 | $5,869.52 |
| 53 | $15,900.00 | $5,623.52 |
| 54 | $16,200.00 | $5,375.26 |
| 55 | $16,500.00 | $5,124.71 |
| 56 | $16,800.00 | $4,871.86 |
| 57 | $17,100.00 | $4,616.68 |
| 58 | $17,400.00 | $4,359.16 |
| 59 | $17,700.00 | $4,099.26 |
| 60 | $18,000.00 | $3,836.98 |
| 61 | $18,300.00 | $3,572.28 |
| 62 | $18,600.00 | $3,305.15 |
| 63 | $18,900.00 | $3,035.56 |
| 64 | $19,200.00 | $2,763.48 |
| 65 | $19,500.00 | $2,488.91 |
| 66 | $19,800.00 | $2,211.81 |
| 67 | $20,100.00 | $1,932.16 |
| 68 | $20,400.00 | $1,649.93 |
| 69 | $20,700.00 | $1,365.11 |
| 70 | $21,000.00 | $1,077.67 |
| 71 | $21,300.00 | $787.59 |
| 72 | $21,600.00 | $494.83 |
| 73 | $21,900.00 | $199.39 |
| 74 | $22,401.22 | $0.00 |
Stephen La Rocque.>
| ||
| ||
| ||
 |
 |
|
Math Central is supported by the University of Regina and The Pacific Institute for the Mathematical Sciences.