If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Annuities
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 |
#2
|
|||
|
|||
Annuities
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 "Chip" wrote in message ... |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 |
#3
|
|||
|
|||
Annuities
699.02 should be 699.06, sorry
-- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | 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 | | "Chip" wrote in message ... ||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 | | |
#4
|
|||
|
|||
Annuities
On Jan 6, 5:11*am, "Niek Otten" wrote:
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. |
Thread Tools | |
Display Modes | |
|
|