Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
Hello
Is there a function I can use in Excel to calculate the payment on a mortgage where; 1. Interest is calculated daily but capitalised quarterly 2. Payments are made monthly I have used the PMT function but it does not take into account that the interest is daily. Any help would be appreciated. Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
Use PMT!
If your interest is 6% yearly, then your daily interest rate is either ..06/360 or .06/365. different countries use different standards. US uses 360 days in the year and number of days in a month as 30. there are 5 days (or 6 in leap years) that arre bank holidays. Other countries use different standards making the calculations more complicated. then use 30 days as the monthly period. for example =PMT(.06/365, 30, $1000) "Beth" wrote: Hello Is there a function I can use in Excel to calculate the payment on a mortgage where; 1. Interest is calculated daily but capitalised quarterly 2. Payments are made monthly I have used the PMT function but it does not take into account that the interest is daily. Any help would be appreciated. Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
On Dec 2, 12:54*am, Beth wrote:
Is there a function I can use in Excel to calculate the payment on a mortgage where; 1. Interest is calculated daily but capitalised quarterly 2. Payments are made monthly I think the answer to your question is "no". But I am having some trouble understanding your situation and requirements completely. I have used the PMT function but it does not take into account that the interest is daily. * The PMT function can be used only when the payment amount is intended to be constant for the entire term of the loan. My understanding of the situation you describe above is: when the interest is capitalized quarterly, the payment amount "may" change. In fact, I don't see how it cannot change. If you have some specific numbers to work with, it might be helpful in interpreting your situation and requirements, perhaps leading to a better answer. Barring that, I would create a monthly amortization schedule. The details can be provided. But they are somewhat complicated and not worth the trouble if this solution does not fit your requirements. I hope you will post back with more information. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
Right now, you have interest compounded quarterly. As you state, the PMT
function (and, in fact, all financial functions) require the payment and interest rate be for the same period. Therefore, you need to convert your interest rate to a compounded monthly rate. Suppose your annual interest rate is 6%. Your compounded quarterly interest rate is therefore 1.5%. If you borrowed $100, then one quarter later, you would owe $101.50. So the question that needs to be answered is: what monthly interest rate turns $100 into $101.50 after three months? The Rate function will answer this, as in: =rate(3,0,100,-101.50) =0.498% Use this in your PMT function, as in: =pmt(rate(3,0,100,-101.50),nper,pv,fv,type) Regards, Fred. "Beth" wrote in message ... Hello Is there a function I can use in Excel to calculate the payment on a mortgage where; 1. Interest is calculated daily but capitalised quarterly 2. Payments are made monthly I have used the PMT function but it does not take into account that the interest is daily. Any help would be appreciated. Thank you |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
On Dec 2, 12:29 pm, "Fred Smith" wrote:
Suppose your annual interest rate is 6%. Your compounded quarterly interest rate is therefore 1.5%. If you borrowed $100, then one quarter later, you would owe $101.50. Doesn't that analysis assume that the principal remains at $100 for the entire quarter? Doesn't the fact that we are making monthly payments and interest is calculated daily change your assumption? Moreover, are you assuming a constant payment amount for the entire term of the loan? ("nper" in your formula?) Is that a valid assumption for this type of loan? I don't know. But a google search for "define: capitalization" indicates that the term means that the unpaid interest is added to the principal, and the payment "may" change. If the payment does not change, I ass-u-me that means that the balloon payment increases when the loan matures. But I have trouble with that interpretation. Whadaya think? ----- original posting ----- On Dec 2, 12:29*pm, "Fred Smith" wrote: Right now, you have interest compounded quarterly. As you state, the PMT function (and, in fact, all financial functions) require the payment and interest rate be for the same period. Therefore, you need to convert your interest rate to a compounded monthly rate. Suppose your annual interest rate is 6%. Your compounded quarterly interest rate is therefore 1.5%. If you borrowed $100, then one quarter later, you would owe $101.50. So the question that needs to be answered is: what monthly interest rate turns $100 into $101.50 after three months? The Rate function will answer this, as in: =rate(3,0,100,-101.50) =0.498% Use this in your PMT function, as in: =pmt(rate(3,0,100,-101.50),nper,pv,fv,type) Regards, Fred. "Beth" wrote in message ... Hello Is there a function I can use in Excel to calculate the payment on a mortgage where; 1. Interest is calculated daily but capitalised quarterly 2. Payments are made monthly I have used the PMT function but it does not take into account that the interest is daily. Any help would be appreciated. Thank you- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
You're looking for problems which don't exist. While compound interest has
complexities, you don't have to make it harder than it is. When you are converting the compounding period, the type of loan, and its payment structure have nothing to do with it. You just have to look at the results to realize the validity of the calculation. If the annual interest rate is 6%, and the loan was compounded monthly, the monthly rate would be 0.5% In this case, with interest compounded quarterly, the monthly rate turns out to be 0.498%. That makes sense. Take a look at the documentation for the holy grail of financial functions -- the HP12C calculator. You'll see it does these conversions the same way. Regards, Fred. "joeu2004" wrote in message ... On Dec 2, 12:29 pm, "Fred Smith" wrote: Suppose your annual interest rate is 6%. Your compounded quarterly interest rate is therefore 1.5%. If you borrowed $100, then one quarter later, you would owe $101.50. Doesn't that analysis assume that the principal remains at $100 for the entire quarter? Doesn't the fact that we are making monthly payments and interest is calculated daily change your assumption? Moreover, are you assuming a constant payment amount for the entire term of the loan? ("nper" in your formula?) Is that a valid assumption for this type of loan? I don't know. But a google search for "define: capitalization" indicates that the term means that the unpaid interest is added to the principal, and the payment "may" change. If the payment does not change, I ass-u-me that means that the balloon payment increases when the loan matures. But I have trouble with that interpretation. Whadaya think? ----- original posting ----- On Dec 2, 12:29 pm, "Fred Smith" wrote: Right now, you have interest compounded quarterly. As you state, the PMT function (and, in fact, all financial functions) require the payment and interest rate be for the same period. Therefore, you need to convert your interest rate to a compounded monthly rate. Suppose your annual interest rate is 6%. Your compounded quarterly interest rate is therefore 1.5%. If you borrowed $100, then one quarter later, you would owe $101.50. So the question that needs to be answered is: what monthly interest rate turns $100 into $101.50 after three months? The Rate function will answer this, as in: =rate(3,0,100,-101.50) =0.498% Use this in your PMT function, as in: =pmt(rate(3,0,100,-101.50),nper,pv,fv,type) Regards, Fred. "Beth" wrote in message ... Hello Is there a function I can use in Excel to calculate the payment on a mortgage where; 1. Interest is calculated daily but capitalised quarterly 2. Payments are made monthly I have used the PMT function but it does not take into account that the interest is daily. Any help would be appreciated. Thank you- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
On Dec 2, 2:54*pm, "Fred Smith" wrote:
You're looking for problems which don't exist. Wouldn't be the first time! :-) You just have to look at the results to realize the validity of the calculation. I did just that, and I confirmed that the structure of your solution is indeed correct. Live and learn! The daily computation of interest introduces only a slight numerical error in the end, which of course can be compensated for in real life by adjusting the last payment. I would suggest one minor change to your formulation. I would compute the monthly rate with RATE(3,0,-1,1+6%/4). Arguably, just a style difference. But I think it is less error-prone than RATE(3,0,1,-1-6%/ 4). Thanks for the clear explanation. I hope the OP benefitted as much as I did. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
I agree. Your rate formula is actually the one I use. When I'm explaining it
to others, I use $100 rather than $1, because I find they understand it better ($101.15 is an easier number to understand than $1.0015). I also agree with your choice of signs. For posterity, we can also show the more general formula: =Rate(12/CompoundingPeriodsPerYear,0,-1,1+AnnualRate/CompoundingPeriodsPerYear) Regards, Fred "joeu2004" wrote in message ... On Dec 2, 2:54 pm, "Fred Smith" wrote: You're looking for problems which don't exist. Wouldn't be the first time! :-) You just have to look at the results to realize the validity of the calculation. I did just that, and I confirmed that the structure of your solution is indeed correct. Live and learn! The daily computation of interest introduces only a slight numerical error in the end, which of course can be compensated for in real life by adjusting the last payment. I would suggest one minor change to your formulation. I would compute the monthly rate with RATE(3,0,-1,1+6%/4). Arguably, just a style difference. But I think it is less error-prone than RATE(3,0,1,-1-6%/ 4). Thanks for the clear explanation. I hope the OP benefitted as much as I did. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mortgage with Quarterly Capitalisation & Monthly Payments
I find it more understandable for pupils to let the signs depend of whether
you are the debitor or the creditor. Here talking about mortgage I guess you are the debitor receiving the $100 and paying the $101,50. The signs therefore must be +100(for filling your pocket), and -101,5(for leaving your pocket) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Monthly Mortgage payments for a Semi annual compounding rate | Excel Worksheet Functions | |||
How do I calculate after making 12 payments of my mortgage | Excel Worksheet Functions | |||
Running Tab using Quarterly Payments | Excel Worksheet Functions | |||
Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) | |||
how do i calculate my mortgage payments | Excel Worksheet Functions |