Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fv & Emi!
i need a formula to get the result for the following: i am getting a loan of Rs10000/- @ 10% pa with monthly rests and the loan is payable in 18 months (15 months for principal and 3 months for interest portion). can i have a formula to find out the EMI for the loan together with the intt as well as the FV of my loan? hlp pl..??!! i give below the meanings for my terminology! 1) "/" nothing but decimal separator "." and "-" means "no decimal" 2) "monthly rests" means interest being applied for monthly products ie, month end balance. 3) interest is applied on "simple interest basis" and not on "compounding effect". ie, interest is applied for the month end balance of principal amount and the interest amount is transferred to another account for which no interest is applied. that's why the repayment is being separately fixed for principal & interest. 4) EMI means Equated Monthly Instalments..? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=500250 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fv & Emi!
"via135" wrote:
can i have a formula to find out the EMI for the loan together with the intt as well as the FV of my loan? Good explanation, this time. But either I do not understand something, or something else is amiss. First, I assume that "10% pa" means 10% per annum. But is that the nominal rate (i.e, 10%/12 monthly), or is it the effective annual rate -- that is, the monthly rate is RATE(12,,-1,1+10%) = (1+10%)^(1/12) - 1? I assume it is the nominal rate. But the bigger question is .... You said that the 10000 rupee principal is paid off in 15 months, and the simple interest on the declining balance is accumulated into another account, which is subsequently paid off in 3 additional months (interest-free!). You are looking for equal monthly payments that pays off both. Right? Based on that description, I model the accumulated interest as follows (i is the period interest rate, 10%/12 in your case, and "pmt" is the monthly payment, what you call EMI): Period 1: 10000 * i Period 2: (10000 - pmt) * i Period 3: (10000 - 2*pmt) * i ..... Period 15: (10000 - 14*pmt) * i Is that what you intended? If so, the formula for the accumulated interest is (n is the number of periods, 15 in your case): int = pmt * i * n * (n+1) / 2 In order to pay off the 10000 principal alone in 15 months, the periodic payment __must__ be 666.67 = 10000 / 15. But there is no a priori guarantee that that periodic payment would pay off the accumulated interest in 3 months -- unless the interest rate had been determined based on that requirement. In fact, with the terms of the loan you stated -- principal of 10000, 10% nominal annual rate, 15 payments -- the accumulated interest is mathematically equal to __one__ payment, 666.67, not taking rounding into account. So the accumulated interest could be paid off in __one__ additional equal payment, not 3. Did you misstate the terms of the loan? That makes more sense to me, from a business perspective. It does not make sense that a lender would permit you to accumulate interest and pay it off over time without charging interest on the remaining balance of unpaid interest. It __does__ make some sense that a lender might structure a loan such that you pay off the principal first, then you pay off the accumulated interest in one more equal payment. The lender loses the cost of capital (interest) on the accumulated interest for one period. But it is simple for both the lender and borrower to understand. Thus, from the lender's perspective, the problem becomes: determine the number of periods, n, or the periodic interest rate, i, to pay off a loan in n periods plus the accumulated interest in k more periods. Interestingly, the loan amount does not matter. i = 2 * k / n / (n+1) n = SQRT((1 + 8*k) / 4 / i) - 0.5 Note: There might a simpler way to approximate n that eludes me at the moment. The periodic payment (pmt, emi) is simply the loan amount divided by the number of periods, n. pmt = prin / n ("prin" is the loan amount) int = pmt * i * n * (n+1) / 2 ("int" is accumulated interest) Of course, all of this is predicated on my understanding of the method for computing accumulated interested above. If that is wrong, all of the above is probably wrong. GIGO. Please let me know if I have any misunderstandings. If not, let me know if you need further explanation or translation into Excel formulas. And please confirm any misstatement of the terms of the loan. ----- "via135" wrote: i need a formula to get the result for the following: i am getting a loan of Rs10000/- @ 10% pa with monthly rests and the loan is payable in 18 months (15 months for principal and 3 months for interest portion). can i have a formula to find out the EMI for the loan together with the intt as well as the FV of my loan? hlp pl..??!! i give below the meanings for my terminology! 1) "/" nothing but decimal separator "." and "-" means "no decimal" 2) "monthly rests" means interest being applied for monthly products ie, month end balance. 3) interest is applied on "simple interest basis" and not on "compounding effect". ie, interest is applied for the month end balance of principal amount and the interest amount is transferred to another account for which no interest is applied. that's why the repayment is being separately fixed for principal & interest. 4) EMI means Equated Monthly Instalments..? -via135 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fv & Emi!
"via135" wrote:
hi JOEU! you are 99.99% on my track! don't look at the loan at business angle. in fact the loan is being disbursed by a company to it's employees just as a concession without any business motive. that's why the loan is being charged with simple interest for the principal only. Okay. In that case, did I answer your questions? If the loan of 10000 is paid off in 15 installments, where each payment is applied entirely to the principal, the monthly payment is necessarily 666.67 = 10000 / 15. You asked for equal payments that would first pay off the loan, then pay off the accumulated interest in 3 additional installments. I explained that the accumulated interest is 666.67 = 666.67 * (10%/12) * 15 * (15+1) / 2. Since the accumulated interest equals the monthly payment, it will take only 1 "equal monthly installment", not 3, to pay off the accumulated interest. Based on the terms of the loan, it is simply not possible to compute a single monthly payment amount that pays off the principal of 10000 in 15 periods and the accumulated interest in 3 periods. But all of that is predicated on my interpretation of the terms and methodology of the loan. To confirm that, I asked several questions, which you have not answered. Please do so. 1. (New question) When you asked for "equal monthly installments", I assumed you are looking for the same monthly installment to pay off both principal and interest in the specified respective time periods. But now I wonder: did you really want to know what the monthly payment would be to pay off the accumulated interest in 3 periods, allowing that monthly payment to be different from the monthly payment to pay off the principal in 15 periods? If that is the case, I should have helped you find that answer by providing the formula above to compute the total accumulated interest. The 3 "interest installments" would simply be 222.22 (approximately) = 666.67 / 3. Do you agree? 2. Is the "10% pa" interest the __nominal__ annual rate? That is, is the monthly rate 10%/12? 3. Do you agree that the balance is reduced according to the following schedule, confirming my formulation, where "pmt" is the monthly payment, what you call "emi": initialBbalance = 10000 Period 1: balance = previousBalance - pmt Period 2: balance = previousBalance - pmt .... Period 15: 0 = previousBalance - pmt 4. Assuming #2 is correct, do you agree that interest accumulates according to the following schedule, confirming my formulation, where "i" is the monthly interest rate: Period 1: int = previousBalance * i Period 2: int = previousInt + (previousBalance * i) .... Period 15: int = previousInt + (previousBalance * i) Note that the interest for the period is based on the previous balance, before the payment for the period. Do you agree? Also note that in each period k, "previousBalance" is the same as initialBalance - (k-1)*pmt, which is what I wrote previously. Finally, you asked for the "FV". That is simply the principal plus accumulated interest. If you agree with all of the above, I had noted earlier that the accumulated interest (int) is: int = pmt * i * n * (n+1) / 2 where "pmt" is the monthly payment (of the principal), "i" is the monthly interest rate, and "n" is the number of payments to pay off the principal (15). Is everything clear? Are all your questions answered? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fv & Emi!
hi JOEU! 1) i am cleared now that i can't expect the same "pmt" for the entire loan period of 18m since the accumulated interest is based on the "pmt" 2) yes..it is 10%/12 3) i agree that the "pmt" for the first 15m will be 666.67 & for the remaining 3m it will be 222.22 4) yes..the FV=principal+acc intt ie, 10000+666.67 i have been cleared 101% ok..for acadamic intt...is there any worksheet function to represent your formula as well as other financial calculations? thks for your prompt followup! -via135 Wrote: "via135" wrote: hi JOEU! you are 99.99% on my track! don't look at the loan at business angle. in fact the loan is being disbursed by a company to it's employees just as a concession without any business motive. that's why the loan is being charged with simple interest for the principal only. Okay. In that case, did I answer your questions? If the loan of 10000 is paid off in 15 installments, where each payment is applied entirely to the principal, the monthly payment is necessarily 666.67 = 10000 / 15. You asked for equal payments that would first pay off the loan, then pay off the accumulated interest in 3 additional installments. I explained that the accumulated interest is 666.67 = 666.67 * (10%/12) * 15 * (15+1) / 2. Since the accumulated interest equals the monthly payment, it will take only 1 "equal monthly installment", not 3, to pay off the accumulated interest. Based on the terms of the loan, it is simply not possible to compute a single monthly payment amount that pays off the principal of 10000 in 15 periods and the accumulated interest in 3 periods. But all of that is predicated on my interpretation of the terms and methodology of the loan. To confirm that, I asked several questions, which you have not answered. Please do so. 1. (New question) When you asked for "equal monthly installments", I assumed you are looking for the same monthly installment to pay off both principal and interest in the specified respective time periods. But now I wonder: did you really want to know what the monthly payment would be to pay off the accumulated interest in 3 periods, allowing that monthly payment to be different from the monthly payment to pay off the principal in 15 periods? If that is the case, I should have helped you find that answer by providing the formula above to compute the total accumulated interest. The 3 "interest installments" would simply be 222.22 (approximately) = 666.67 / 3. Do you agree? 2. Is the "10% pa" interest the __nominal__ annual rate? That is, is the monthly rate 10%/12? 3. Do you agree that the balance is reduced according to the following schedule, confirming my formulation, where "pmt" is the monthly payment, what you call "emi": initialBbalance = 10000 Period 1: balance = previousBalance - pmt Period 2: balance = previousBalance - pmt .... Period 15: 0 = previousBalance - pmt 4. Assuming #2 is correct, do you agree that interest accumulates according to the following schedule, confirming my formulation, where "i" is the monthly interest rate: Period 1: int = previousBalance * i Period 2: int = previousInt + (previousBalance * i) .... Period 15: int = previousInt + (previousBalance * i) Note that the interest for the period is based on the previous balance, before the payment for the period. Do you agree? Also note that in each period k, "previousBalance" is the same as initialBalance - (k-1)*pmt, which is what I wrote previously. Finally, you asked for the "FV". That is simply the principal plus accumulated interest. If you agree with all of the above, I had noted earlier that the accumulated interest (int) is: int = pmt * i * n * (n+1) / 2 where "pmt" is the monthly payment (of the principal), "i" is the monthly interest rate, and "n" is the number of payments to pay off the principal (15). Is everything clear? Are all your questions answered? -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=500250 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fv & Emi!
"via135" wrote:
is there any worksheet function to represent your formula as well as other financial calculations? None that I am aware of for the formulas that I developed for this loan structure. As for "other financial calculations", there is a plethora of formulas including FV(), PMT(), NPER(), and RATE(). Use Excel Help to learn about those functions and others. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fv & Emi!
thks JOEU! regds! -via135 Wrote: "via135" wrote: is there any worksheet function to represent your formula as well as other financial calculations? None that I am aware of for the formulas that I developed for this loan structure. As for "other financial calculations", there is a plethora of formulas including FV(), PMT(), NPER(), and RATE(). Use Excel Help to learn about those functions and others. -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=500250 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|