Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 623
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 947
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 947
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 623
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
loan calculator, int rate changes and fixed prin payment skip770 Excel Discussion (Misc queries) 0 December 15th 05 03:41 PM
how do I calculate a monthly payment based on a variable rate? Chick N Egg Excel Worksheet Functions 1 November 17th 05 10:00 PM
Calculate Interest Rate when payment changes! huntermcg Excel Worksheet Functions 2 October 25th 05 08:32 AM
calculate payment with first payment due date variable? Jody Solbach Excel Worksheet Functions 1 September 8th 05 05:46 PM
how do you get a positive number payment with a mortgage payment . sam Excel Worksheet Functions 1 February 2nd 05 06:32 AM


All times are GMT +1. The time now is 11:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"