   SEARCH HOME Math Central Quandaries & Queries  Question from Ema: 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 (1.0025) -$300

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.