IMPT question with more detail.

[Colette]

here is my basic config

B3 Savings Goal: $75,000
B4 Interest Rate 6.50%
B5 Number of Years: 18
B6 Payments per Year: 12
B7 Total Payments: 216

B9 Monthly Payment: $183.67

Then on my schedule it looks like this
Period Starting Balance Interest Ending Balance
1 $183.67 $10.22 $193.89
2 $377.56 $9.21 $386.77

But this is not correct. My intrest should be growing
and yet it is not. the function of IPMT in the interest
column is =IPMT($B$4/$B$6,$B$6,$B$7,F2,B3,1)

and i should be able to auto fill for 18 years. What is
wrong?

 

[Harlan Grove]

here is my basic config

B3 Savings Goal: $75,000
B4 Interest Rate 6.50%
B5 Number of Years: 18
B6 Payments per Year: 12
B7 Total Payments: 216

B9 Monthly Payment: $183.67

Then on my schedule it looks like this
Period Starting Balance Interest Ending Balance
1 $183.67 $10.22 $193.89
2 $377.56 $9.21 $386.77

First, you're giving yourself credit for 6.5% interest per *month* rather
than per year in these formulas. You need to fix that.

Next, IPMT is geared towards loan repayment rather than funds accumulation.
That is, IPMT assumes a declining balance on which interest is calculated in
each period, so interest is declining over time. Don't use IPMT.

Try this instead. Enter 183.67 in A1, =ROUND(A1*0.065/12,2) in B1, =A1+B1 in
C1, then =A$1+C1 in A2, and copy B1:C1 down into B2:C2. Select A2:C2 and
fill down into A3:C217. A217 should be the accumulated amount, and it won't
be exactly $75,000.00, but it'll be within $1.00.