Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
XCEL 2 calculate week or bi-weekly accel. mortgage payment
I need a spreadsheet to help calculate what the interest cost and remaining
balance of a mortgage would be after a certain period 9similar to an amortizationtable). Available templates do not offer the option to enter my own payment and the frequency of the payment i.e. monthly, bi-weekly or weekly. I need to be able to enter my own payment amount and the frequency 12,24,26,52 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
XCEL 2 calculate week or bi-weekly accel. mortgage payment
"commuter" wrote:
I need a spreadsheet to help calculate what the interest cost and remaining balance of a mortgage would be after a certain period (similar to an amortization table). Available templates do not offer the option to enter my own payment and the frequency of the payment i.e. monthly, bi-weekly or weekly. I need to be able to enter my own payment amount and the frequency 12,24,26,52 There are several different problems to solve: 1. Computing the required payment and estimating the number of payments. 2. Generating the appropriate column of payment dates. 3. Generating the rest of the amortization table. You only ask about #2 and #3. Once you have #2 (see below), #3 is quite easy. It is the same in all cases. If A:A is the date, B:B is the payment, C:C is the prinicipal paid in the period, D:D is the interest paid in the period, E:E is the remaining balance, then set A1 and E1 to the date and amount of the initial loan, enter the following formulas and copy down: C2: =B2-D2 D2: =D1*(1+rate/365)^(A2-A1) E2: =D1+D2-B2 where "rate" is the nominal annual interest rate; "rate" might be a cell reference. This assumes that loans compound daily. Alternatively, ignoring daily compounding, you might replace D2 with simply D1*(1+rate/freq), where "freq" is 12, 24, 26 or 52 (or a cell reference). #2 can be more difficult, namely: generating the dates in A:A in a general manner. I would suggest entering the first two dates into A1 and A2, then entering the following formula into A3 and copying down: =if (or(freq=12, freq=24), date(year(A1), 1+month(A1), day(A1)), A1+14) where "freq" is the number 12, 24, 26 or 52; "freq" might be a cell reference. If "freq" is 12 or 24, we want payments on the same day one month apart every other payment. If "freq" is 26 or 52, we want payments 14 days apart every other payment. By entering the first two dates, this computes monthly and weekly payment schedules, even though the logic seems to only compute bimonthly and biweekly schedules. #1 can be the most difficult, if only because lender policy usually differs from mathematical reality. That is, the way that lenders compute the payment does not conform to the mathematical-correct formula to reduce the loan balance to zero in the prescribed number of payments, taking daily compounding into account. I believe the following conforms to what most US lenders do: =pmt(rate/freq, n, -loan) where "n" is the number of payments (nominally term*freq, where "term" is in years) and "loan" is the initial loan amount. A more mathematically-correct formula would be (for the US): =pmt(fv(rate/365, 365/freq,, -1) - 1, n, -loan) Note that even that formula is not mathematically correct because no payment period has exactly 365/freq days. NPER() can be used to estimate the number of payments, given the payment amount and the periodic rate computed by one of the methods above, namely fv(rate/freq,...)-1 or rate/freq. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
XCEL 2 calculate week or bi-weekly accel. mortgage payment
I wrote:
I believe the following conforms to what most US lenders do: =pmt(rate/freq, n, -loan) I just learned that the OP is asking about Canadian mortgages. (I had a premonition that that might be the case.) Based on a comparison with one online Canadian mortgage calculator, I believe the following would be the Canadian formula: =pmt(rate(freq/2,, -1, 1 + r/2), n, -loan) where "r" is the annual rate; note the name change to avoid confusion with the RATE() function. But note that this is for a "regular" frequency (weekly, biweekly etc). I do not know what the "accel" frequency is and how it differs, other than the fact that it results in slight higher payment and shorter term. Google searches have not yielded any insightful information. (In hindsight, I see that "accel" was mentioned in the subject line, but not in the body of the posting.) Can anyone shed some light on "accel" v. "regular" (my term) weekly/biweekly payment schedules for Canadian mortgages? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
XCEL 2 calculate week or bi-weekly accel. mortgage payment
Canadian mortgages are compounded "every six months, not in advance". So a
mortgage quoted as 6% is actually 3% compounded semiannually. If payments are anything other than semi-annual (which they almost always are), you need to convert the quoted rate to a periodic rate, as in: =rate(annualperiods/2,0,-1,1+annualrate/2) -- Regards, Fred " wrote in message ... I wrote: I believe the following conforms to what most US lenders do: =pmt(rate/freq, n, -loan) I just learned that the OP is asking about Canadian mortgages. (I had a premonition that that might be the case.) Based on a comparison with one online Canadian mortgage calculator, I believe the following would be the Canadian formula: =pmt(rate(freq/2,, -1, 1 + r/2), n, -loan) where "r" is the annual rate; note the name change to avoid confusion with the RATE() function. But note that this is for a "regular" frequency (weekly, biweekly etc). I do not know what the "accel" frequency is and how it differs, other than the fact that it results in slight higher payment and shorter term. Google searches have not yielded any insightful information. (In hindsight, I see that "accel" was mentioned in the subject line, but not in the body of the posting.) Can anyone shed some light on "accel" v. "regular" (my term) weekly/biweekly payment schedules for Canadian mortgages? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
XCEL 2 calculate week or bi-weekly accel. mortgage payment
Fred Smith wrote:
Canadian mortgages are compounded "every six months, not in advance". So a mortgage quoted as 6% is actually 3% compounded semiannually. If payments are anything other than semi-annual (which they almost always are), you need to convert the quoted rate to a periodic rate, as in: =rate(annualperiods/2,0,-1,1+annualrate/2) Obviously I know that since that is what I wrote in my updated posting, which you quote. But that does nothing to improve my understanding of the difference between "accel" v. "regular" weekly or biweekly payment schedules. No matter: I believe I discovered the answer myself. Based on one online Canadian calculator, the rate() computation above (and in my earlier posting) is useful for computing pmt() for "regular" periodic payments. Thus, "regular" weekly/biweekly payments are computed using normal amortization, assuming the payments go on for the full loan term. With "accel" weekly/biweekly schedules, the payment amount seems to be based on the total monthly payments for 13 months (i.e. a year plus one month), based the original loan term (e.g. 25 yr). That total is divided by the number of "accel" payments (26 or 52). The "accel" schedules are marketed as "allow[ing] you to make the equivalent of 13 months of payments over a 12-month period" -- similar to the way semimonthly payments (at least) are marketed in the US. The effect is to greatly reduce the total interest paid over the life of the loan, as well as to shorten the actual length of the loan. For example, for a $100,000 Canadian loan at 6% over 25 yr nominally the monthly payment is $639.81 for 300 payments and less than $91,942 total interest, the "regular" biweekly payment is $294.90 for 650 payments and less than $91,687 interest, the "accel" biweekly payment is $319.90 for 547 payments (21.04 yr) and less than $74,987 interest, and the "accel" weekly payment is $159.95 for 1093 (21.02 yr) and less than $74,827 interest, which closely correspond to the results of the online Canadian mortgage calculator. Thus, the Excel formulas for Canadian "accel" schedules a =13*pmt(rate(12/2,,-1,1+r/2), 12*term, -loan) / freq =roundup(nper(rate(freq/2,,-1,1+r/2), p, -loan), 0) where "r" is the annual rate, "p" is the payment (the result of the pmt() formula), and "term" is the nominal length (in years) of the loan based on monthly payments. The nper() formula yields "n", i.e. the number of payments, which I used in other formulas in my previous posting. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
Macro to insert copied cells | Excel Discussion (Misc queries) | |||
need a function to calculate a mortgage payment | Excel Worksheet Functions | |||
need a function to calculate a mortgage payment | Excel Worksheet Functions | |||
Trying to Manipulate a Mortgage Payment (HELP!) | Excel Worksheet Functions |