Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function
I need to calculate a payment on a loan where the customer puts 2 payments in
advance. How would I incorporate the advanced payment sinto the formlula for PMT? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function
Reduce the initial balance by the two payments
-- Gary''s Student - gsnu200814 "Micros" wrote: I need to calculate a payment on a loan where the customer puts 2 payments in advance. How would I incorporate the advanced payment sinto the formlula for PMT? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function
On Nov 18, 10:04*am, Micros wrote:
I need to calculate a payment on a loan where the customer puts 2 payments in advance. How would I incorporate the advanced payment sinto the formlula for PMT? Kind of a circular question. As you know, the PMT is normally a periodic amount that will reduce the initial balance (PV) to zero (or a specified balloon payment) based on the loan term and interest rate. Making 2 payments of that amount reduces the outstanding balance and the remaining term of the loan. But the periodic PMT is not normally recalculated -- unless you are refinancing. (By the way, normally, if more than one payment is made in advance, the principal is reduced by the full amount of the extra payments.) So I wonder what your real question is. Do you want to know the new term of the loan? Use NPER, reducing the outstanding balance appropriately. Note: You said "2 payments in advance". That's ambiguous. If the normal payment is $1000, which do you mean you paid in advance: a total of $2000 or $3000? The first $1000 is normally considered payment of both interest and principal as if paid on the due date. The remainder ($1000 or $2000) is considered payment of principal. But check your loan agreement. It might say something different. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function
Errata ....
On Nov 18, 11:45 am, I wrote: Note: You said "2 payments in advance". That's ambiguous. If the normal payment is $1000, which do you mean you paid in advance: a total of $2000 or $3000? The first $1000 is normally considered payment of both interest and principal as if paid on the due date. Although generally true for interstitial "advance payments", this probably does not apply to the case you are talking about. All of any amount paid at the outset of a loan reduces the principal. What I was trying to say before is: you cannot compute payment based on the __initial__ loan such that it reduces the __initial__ loan to zero in n periods, and it reduces the __reduced__ loan to zero in the same n periods, where the reduced loan is the initial loan less twice that payment. However, you __can__ compute a payment that reduces (only) the __reduced__ loan to zero in n periods such that the reduced loan amount is the initial loan less twice that payment. That's probably what you want. I doubt that you can compute that payment using PMT. However, you can use the following formula to compute the payment: =-pv*(1+rt)^n * rt / ( (1+2*rt)*(1+rt)^n - 1 ) where rt = i/n, i = annual interest rate, pv = initial loan amount (positive number), and n = term of the loan. Note that that computes payment as a negative number. If you want a positive number, change "-pv..." to "pv..." (eliminate the unary minus). BTW, the factor "2*rt" can be replaced by "k*rt", were k = number of advance payments. HTH. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT function
On Nov 18, 4:24 pm, I wrote:
you can use the following formula to compute the payment: =-pv*(1+rt)^n * rt / ( (1+2*rt)*(1+rt)^n - 1 ) where rt = i/n, i = annual interest rate, pv = initial loan amount (positive number), and n = term of the loan. A numerical example might help. Consider a loan of $100,000 over 15 years at 6%. The monthly payment would be about $843.86. Then you might say that the monthly payment can be reduced to about $829.85 if the customer makes 2 advance payments totaling $1659.70. But all you really have done is transform the $100,000 loan into a loan of $98,340.30. And it always true that if you reduce the principal of a loan over the same term at the same annual interest rate, the periodic payments are reduced. Why limit the customer to "2 advance payments"? Is that just a sales gimmick? If you tell the customer that he can reduce the payments by making a "down payment" of any amount, then you can use the PMT function with a "pv" of 100000 less the "down payment", as Gary said. If you want to ensure that the customer pays a minimum down payment equivalent to 2 payments, you can use my formula to compute that minimum. HTH. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |