Annuities

[Chip]

I need help to make a function in Excel to do an annuity table for 218 month
showing each month the monthly payment divided in 2 figures: the down payment
for the loan and amount of the interest. The loan is 100.000 DDK; the annual
interest rate is 5%, the number of payments is 218 and the monthly payment
700 DDK. I’ll be happy if you can help me.
Chip

 

[Niek Otten]

Hi Chip,

In D1: 100000
In A2: =D1
In B2: =5%/12*A2 This is the rent
In C2: =700 - B2 This the down payment
In D2: =A2-C2

Copy row 2 down to row 219

You'll find that it doesn't get exactly to zero. To get to zero, you'd have to pay 699.02, as can be checked with the PMT()
function


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I need help to make a function in Excel to do an annuity table for 218 month
| showing each month the monthly payment divided in 2 figures: the down payment
| for the loan and amount of the interest. The loan is 100.000 DDK; the annual
| interest rate is 5%, the number of payments is 218 and the monthly payment
| 700 DDK. I'll be happy if you can help me.
| Chip

 

[Niek Otten]

699.02 should be 699.06, sorry

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Chip,
|
| In D1: 100000
| In A2: =D1
| In B2: =5%/12*A2 This is the rent
| In C2: =700 - B2 This the down payment
| In D2: =A2-C2
|
| Copy row 2 down to row 219
|
| You'll find that it doesn't get exactly to zero. To get to zero, you'd have to pay 699.02, as can be checked with the PMT()
| function
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||I need help to make a function in Excel to do an annuity table for 218 month
|| showing each month the monthly payment divided in 2 figures: the down payment
|| for the loan and amount of the interest. The loan is 100.000 DDK; the annual
|| interest rate is 5%, the number of payments is 218 and the monthly payment
|| 700 DDK. I'll be happy if you can help me.
|| Chip
|
|

 

[joeu2004]

In D1: 100000
In A2: =D1
In B2: =5%/12*A2 This is the rent
In C2: =700 - B2 This the down payment
In D2: =A2-C2
Copy row 2 down to row 219

You'll find that it doesn't get exactly to zero. To get to zero,
you'd have to pay [699.06], as can be checked with the PMT()
function

Even then, the balance is not likely to be "exactly" zero because the
PMT() result must be rounded (perhaps up or down, TBD by the lender)
at least to the smallest coin of the realm (ore? [1]; or greater,
again TBD by the lender)

More to the point, the last payment is almost never exactly the same
as the regular payment in order to pay off the balance. For that
reason, I prefer to build that into the annuity table formulas. So I
would suggest at least the following [2].

A1: Loan B1: 100000
A2: Periodic Rate B2: =5.00%/12
A3: Payment B3: 700
A4: Stated Term B4: 218
A5: Actual Term B5: =min(B4, roundup(nper(B2, B3, -B1),0))

A7: Payment Number
B7: Payment Paid
C7: Interest Paid
D7: Principal Paid
E7: Balance

E8: =A1

A9: 1
B9: =if(A9=$B$5, roundup(E8*(1+$B$2),2), $B$3)
C9: =if(A9=$B$5, B9-E8, E8*$B$2)
D9: =if(A9=$B$5, E8, B9-C9)
E9: =E8-D9

Copy A9:E9 down until the cell in column A is the same as B5. (If you
drag the Fill Handle, watch the Name Box in the Formula Bar.)

HTH.


Endnotes:

[1] I don't know what monetary unit DDK is. It is not defined by the
ISO 4217 standard. I ass-u-me the OP mean DKK -- the Danish kroner.

[2] I actually use more complex formulas that turn the annuity table
in a template, among other changes.