help with interest calculation

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I have a spreadsheet where I have to calculate payments
over a 40-month period to a number of parties. Interest
is constant at 4.9%. No payments for next six months,
then payments to the group of 20 totaling $X, followed by
payments to the group of 20 totaling $Y. I need to show
principal and interest paid to each party in the
respective time periods. Any help in setting up formula
or formulas would be appreciated.
 
I would set up an amortization table, with one row for each month and a
column for each party.

Each row would have the period, the opening balance, interest charged that
period, payments (if any) and closing balance.

Interest is simply Opening * 4.9% / 12.
Closing balance is Opening + Interest - Payments
Initial opening balance is the amount of the loan, in subsequent periods,
it's the closing balance of the previous period.
 
-----Original Message-----
I have a spreadsheet where I have to calculate payments
over a 40-month period to a number of parties. Interest
is constant at 4.9%. No payments for next six months,
then payments to the group of 20 totaling $X, followed by
payments to the group of 20 totaling $Y. I need to show
principal and interest paid to each party in the
respective time periods. Any help in setting up formula
or formulas would be appreciated.
.
Click to expand...
Bill

Excel has a number of financial formulas including this.

Principal 4000
Interest 4.9
Payment ($148.64)

=PPMT(B3/12,34,40,B2)

Hope this helps
 
Thanks for the excellent suggestions. however, I'm still
stuck, as the unsolved issue is that it's a two-step
payment plan. That is, total payments to the 20
creditors will equal $3000 for the first 15 months, and
$4500 for the next 30 months. Ultimately, I have to show
a table (i.e., x will receive 15 payments of $123.45, 30
payments of 234.56, and total payments over life of plan.

I keep hoping Excel will have some way out of this, some
kind of actuarial formula or something, but so far, no
luck.

But thanks again for your excellent suggestion!
 
An amortization table doesn't care how often the payment changes. You could
change it every month and it will still work. A normal amortization table
would have one column for the payment. In your case, I would set up one
column for each creditor, then another column with the sum of all payments.

Or is your problem that you need to calculate what payment will amortize the
loan over a specific period? If so, I would use Goal Seek to do this.
 
Hi Bill!

You question is very confusing but I think there is probably a formula
solution but we need to clarify a few points.

Can I take it that there is an existing debt of 7500 to each of 20
creditors and that this debt carries interest at 4.9% Nominal
compounded monthly.

This debt is to be redeemed by 15 payments of $200 principal and 30
payments of $150 principal. Thus total principal payments are $7500.

When do the creditors receive interest on the outstanding balance?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
Back
Top