Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment is kn
Does anyone know of a function that can provide the the starting payment and
the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment is kn
assuming you want a ten percent increase each year based on that years payment
to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) to get the second payment =1.1* this cell and so forth "Shailendra Harri" wrote: Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment is kn
You have a simple future value problem. Your future value is 5000, interest rate
10% and term 5 years. Use: =pmt(10%,5,0,-5000,0) -- Regards, Fred "Shailendra Harri" wrote in message ... Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
Dear BJ,
Thank you for the prompt response, however your suggestion returns first payment 147.30 when the result should be 818.99. I think we on the right track here, but somehow the function is not 100% correct. Also it seems you have used some Algebra type formuals here which are a lot more simpler than what i have used. Can you please explain your logic so that i can understand this. -- Thank you in advance "bj" wrote: assuming you want a ten percent increase each year based on that years payment to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) to get the second payment =1.1* this cell and so forth "Shailendra Harri" wrote: Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
have used some Algebra type formuals here which are a lot more simpler
than what i have used. If you are looking for the Formula: =(10%*5000)/((10%+1)^5-1) -818.89 -- HTH :) Dana DeLouis Windows XP & Excel 2007 "Shailendra Harri" wrote in message ... Dear BJ, Thank you for the prompt response, however your suggestion returns first payment 147.30 when the result should be 818.99. I think we on the right track here, but somehow the function is not 100% correct. Also it seems you have used some Algebra type formuals here which are a lot more simpler than what i have used. Can you please explain your logic so that i can understand this. -- Thank you in advance "bj" wrote: assuming you want a ten percent increase each year based on that years payment to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) to get the second payment =1.1* this cell and so forth "Shailendra Harri" wrote: Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
other than I added one too many ")" at the end of the formula in the response
when I typed it I am not sure why you get 147.30 I get 818.98 with the equation as printed minus the ")" I can get 147.30 with =5000/SUMPRODUCT(1.1^(ROW(A19:A23)-1)) the function is the same as 5000/(1.1+1.1^2+1.1^3+1.1^4+1.1^5) since row(A1)=1 row(A2) =2 etc so 1.1^(row(A1)-1)=1.1^(1-1)=1.1^(0)=1.1 and sumproduct was a convenient function to use for summing) "Shailendra Harri" wrote: Dear BJ, Thank you for the prompt response, however your suggestion returns first payment 147.30 when the result should be 818.99. I think we on the right track here, but somehow the function is not 100% correct. Also it seems you have used some Algebra type formuals here which are a lot more simpler than what i have used. Can you please explain your logic so that i can understand this. -- Thank you in advance "bj" wrote: assuming you want a ten percent increase each year based on that years payment to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) to get the second payment =1.1* this cell and so forth "Shailendra Harri" wrote: Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
Dear BJ,
I have figured out that the formula you suggested must always start in row 1, since row A1 = 1 and using the formula in any other row will result in incorrect calcuation. Thank You for your assistance in this "bj" wrote: other than I added one too many ")" at the end of the formula in the response when I typed it I am not sure why you get 147.30 I get 818.98 with the equation as printed minus the ")" I can get 147.30 with =5000/SUMPRODUCT(1.1^(ROW(A19:A23)-1)) the function is the same as 5000/(1.1+1.1^2+1.1^3+1.1^4+1.1^5) since row(A1)=1 row(A2) =2 etc so 1.1^(row(A1)-1)=1.1^(1-1)=1.1^(0)=1.1 and sumproduct was a convenient function to use for summing) "Shailendra Harri" wrote: Dear BJ, Thank you for the prompt response, however your suggestion returns first payment 147.30 when the result should be 818.99. I think we on the right track here, but somehow the function is not 100% correct. Also it seems you have used some Algebra type formuals here which are a lot more simpler than what i have used. Can you please explain your logic so that i can understand this. -- Thank you in advance "bj" wrote: assuming you want a ten percent increase each year based on that years payment to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) to get the second payment =1.1* this cell and so forth "Shailendra Harri" wrote: Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
Dear Dana, Thank you for this, this is a simpler formula to what i have used. "Dana DeLouis" wrote: have used some Algebra type formuals here which are a lot more simpler than what i have used. If you are looking for the Formula: =(10%*5000)/((10%+1)^5-1) -818.89 -- HTH :) Dana DeLouis Windows XP & Excel 2007 "Shailendra Harri" wrote in message ... Dear BJ, Thank you for the prompt response, however your suggestion returns first payment 147.30 when the result should be 818.99. I think we on the right track here, but somehow the function is not 100% correct. Also it seems you have used some Algebra type formuals here which are a lot more simpler than what i have used. Can you please explain your logic so that i can understand this. -- Thank you in advance "bj" wrote: assuming you want a ten percent increase each year based on that years payment to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) to get the second payment =1.1* this cell and so forth "Shailendra Harri" wrote: Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
Dear Fred,
Thank you for this, I looked at this formula and tried this before but could not figure this out, as the unknown was the present value. But I have learnt something from this as the present value in your formula is "0". Can you explain the logic here, i am assuming this is because this value is unknown and can be replaced with "0". -- Thank you in advance "Fred Smith" wrote: You have a simple future value problem. Your future value is 5000, interest rate 10% and term 5 years. Use: =pmt(10%,5,0,-5000,0) -- Regards, Fred "Shailendra Harri" wrote in message ... Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
...the formula you suggested must always start in row 1...
...and using the formula in any other row will result in incorrect calcuation. Just some thoughts... To avoid such an issue as you described, that technique is usually written like this. =5000/SUMPRODUCT(1.1^(ROW(INDIRECT("1:5"))-1)) ... 1.1+1.1^2+1.1^3+1.1^4+1.1^5 However, such series have a closed form. Sounds like this was the issue you were facing. Do a Google search for " Geometric Series" What you were looking for was something like this: total = start * (Geometric Series) Than, solve for start value. You will recognize the Geometric Series formula in Excel's help under the PV function. -- HTH :) Dana DeLouis Windows XP & Excel 2007 "Shailendra Harri" wrote in message ... Dear BJ, I have figured out that the formula you suggested must always start in row 1, since row A1 = 1 and using the formula in any other row will result in incorrect calcuation. Thank You for your assistance in this "bj" wrote: other than I added one too many ")" at the end of the formula in the response when I typed it I am not sure why you get 147.30 I get 818.98 with the equation as printed minus the ")" I can get 147.30 with =5000/SUMPRODUCT(1.1^(ROW(A19:A23)-1)) the function is the same as 5000/(1.1+1.1^2+1.1^3+1.1^4+1.1^5) since row(A1)=1 row(A2) =2 etc so 1.1^(row(A1)-1)=1.1^(1-1)=1.1^(0)=1.1 and sumproduct was a convenient function to use for summing) "Shailendra Harri" wrote: Dear BJ, Thank you for the prompt response, however your suggestion returns first payment 147.30 when the result should be 818.99. I think we on the right track here, but somehow the function is not 100% correct. Also it seems you have used some Algebra type formuals here which are a lot more simpler than what i have used. Can you please explain your logic so that i can understand this. -- Thank you in advance "bj" wrote: assuming you want a ten percent increase each year based on that years payment to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) to get the second payment =1.1* this cell and so forth "Shailendra Harri" wrote: Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
On Sep 22, 12:14 am, Shailendra Harri
wrote: "bj" wrote: assuming you want a ten percent increase each year based on that years payment to get the first payment =5000/sumproduct(1.1^(row(A1:A5)-1))) I have figured out that the formula you suggested must always start in row 1, since row A1 = 1 and using the formula in any other row will result in incorrect calcuation. Notwithstanding the fact that the formula is overkill, you might have run into a problem if you copy-and-pasted the formula. The relative range A1:A5 should be written as an absolute range, namely: =5000/sumproduct(1.1^(row($A$1:$A$5)-1)) In this context, row($1:$5) always works. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
On Sep 22, 8:00 am, joeu2004 wrote:
"bj" wrote: =5000/sumproduct(1.1^(row(A1:A5)-1))) Notwithstanding the fact that the formula is overkill,[....] "Overkill" was a poor choice of words. I think BJ's solution is the most natural, given the problem description. It is just that it can be simplified, as others have demonstrated. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Starting payment when increase rate is known & total payment i
It's because there is no present value. You are starting from zero. It's
actually very common in PMT formulas. -- Regards, Fred "Shailendra Harri" wrote in message ... Dear Fred, Thank you for this, I looked at this formula and tried this before but could not figure this out, as the unknown was the present value. But I have learnt something from this as the present value in your formula is "0". Can you explain the logic here, i am assuming this is because this value is unknown and can be replaced with "0". -- Thank you in advance "Fred Smith" wrote: You have a simple future value problem. Your future value is 5000, interest rate 10% and term 5 years. Use: =pmt(10%,5,0,-5000,0) -- Regards, Fred "Shailendra Harri" wrote in message ... Does anyone know of a function that can provide the the starting payment and the subsequent payments thereafter to reach a total as defined. Example: Total payments must be 5000 over 5 years. The increase rate is 10% every year, so what will the starting payment be. I have been able to figure this out using Algebra type mathematical formula's in excel, however i believe there must be a function that can very easily achieve this more quickly. The field Total payments, no of payments and increase rate is known, while the unknown is the starting payment and the payments subsequent to that. -- Thank you in advance Shailendra Harri |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loan calculator, int rate changes and fixed prin payment | Excel Discussion (Misc queries) | |||
how do I calculate a monthly payment based on a variable rate? | Excel Worksheet Functions | |||
Calculate Interest Rate when payment changes! | Excel Worksheet Functions | |||
calculate payment with first payment due date variable? | Excel Worksheet Functions | |||
how do you get a positive number payment with a mortgage payment . | Excel Worksheet Functions |