Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default financial function ..please

Hello all,

I am in challenge with my boss if i can solve this .. so i want sone
one to help me and then i will understand it and know how it works to
expecdt in==any futuire questions from him in this matter.

=================
.. If a company borrow 30,000,000 $ from a bank.

.. It will pay monthly to the bank 3,000,000 $

.. The period of the loan is 15 years.

.. By the end of the loan he will found that have paid 45,000,000 $.

.. The interest rate is not fixed but getting decrease every year.

= So he want to know how the interest rate will be in the first,
seconde and the third year ..until the year 15.

=================

So, i need toi know please how to do it .. i want to understand to be
ready for future questions.

Thanks very much,

Bassem


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default financial function ..please

Unless you specify how the interest decreases, this problem has an infinite number of solutions. Another way of saying that is
that it can not be solved.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hello all,
|
| I am in challenge with my boss if i can solve this .. so i want sone
| one to help me and then i will understand it and know how it works to
| expecdt in==any futuire questions from him in this matter.
|
| =================
| . If a company borrow 30,000,000 $ from a bank.
|
| . It will pay monthly to the bank 3,000,000 $
|
| . The period of the loan is 15 years.
|
| . By the end of the loan he will found that have paid 45,000,000 $.
|
| . The interest rate is not fixed but getting decrease every year.
|
| = So he want to know how the interest rate will be in the first,
| seconde and the third year ..until the year 15.
|
| =================
|
| So, i need toi know please how to do it .. i want to understand to be
| ready for future questions.
|
| Thanks very much,
|
| Bassem
|
|


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default financial function ..please

Dear Niek,

Thanks very much for your reply,

But if you please give me a sample if the interest rate is decrease ..
so how i can solved or i mean how its work so if i meet him tomorrow
and he give me data on how it may decrease so i can solve it while
setting with him.


Best Regards,
Bassem



Niek Otten wrote:
Unless you specify how the interest decreases, this problem has an infinite number of solutions. Another way of saying that is
that it can not be solved.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hello all,
|
| I am in challenge with my boss if i can solve this .. so i want sone
| one to help me and then i will understand it and know how it works to
| expecdt in==any futuire questions from him in this matter.
|
| =================
| . If a company borrow 30,000,000 $ from a bank.
|
| . It will pay monthly to the bank 3,000,000 $
|
| . The period of the loan is 15 years.
|
| . By the end of the loan he will found that have paid 45,000,000 $.
|
| . The interest rate is not fixed but getting decrease every year.
|
| = So he want to know how the interest rate will be in the first,
| seconde and the third year ..until the year 15.
|
| =================
|
| So, i need toi know please how to do it .. i want to understand to be
| ready for future questions.
|
| Thanks very much,
|
| Bassem
|
|


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 418
Default financial function ..please

wrote:
I am in challenge with my boss if i can solve this


First, if you are dealing with millions of dollars (or any monetary
unit), you should be getting professional accounting help. You
certainly can afford it -- especially if you are the lender. But I
wonder if this is really a homework problem. It sounds a little too
contrived. No matter; we can have fun with it.

If a company borrow 30,000,000 $ from a bank.
It will pay monthly to the bank 3,000,000 $
The period of the loan is 15 years.
By the end of the loan he will found that have paid 45,000,000 $.


Those numbers do not make sense. I presume you mean to say: you will
make monthly payments totally 3M per year.

The interest rate is not fixed but getting decrease every year.
So he want to know how the interest rate will be in the first,
seconde and the third year ..until the year 15.

So, i need toi know please how to do it .. i want to understand to be
ready for future questions.


There are an infinite number of solutions. But you have already placed
some constraints on the solution. One way to find some solutions is to
place some additional constraints on the minimum and maximum interest
rates, then set up a spreadsheet and play "what if" games with the
numbers. For example....

If the monthly payments total 3M per year, the average monthly payment
must be 250,000 (3M/12). Thus, an annual nominal rate of 5.80% would
pay off the loan in 15 years, computed by:

=12*rate(15*12, -250000, 30e6)

If we decreased the interest rate from that point, the loan would be
paid off sooner. Therefo (1) the monthly payments must be allowed
to vary (but still total 3M per year); or (2) the annual total of
monthly payments must be allowed to be less than 3M sometimes; or (3)
the initial interest rate must be higher than 5.80%. And perhaps a
combination of all three are needed and acceptable.

Since you specified an annual total of 3M in monthly payments, I would
use a constant monthly payment of 250,000 and set the initial interest
rate higher than 5.80%. But how much higher depends on how much the
interest rate can decrease each year and how low you want it go. Both
constraints are unspecified.

I set up a spreadsheet with the following form:

1. One table with 181 rows, one for each month and one for the initial
balance. The columns are month number (0 through 180), interest, and
balance.

2. Another table with 15 rows. One column is the first month number
(1, 13, ..., 169) to which an interest rate applies; the other column
is the nominal annual interest rate (see #5 below).

3. The second column (interest) of the first table has the following
formula:

=prevBalance * vlookup(monthNumber, table2, 2) / 12

4. The third column (balance) of the first table is simply:

=prevBalance + interest - 250000

5. I arbitrarily set the second column (interest rate) of the second
table such that the lowest rate (last row) is 0.5%, and the interest
rate increases 0.5% in each row above. Thus, the highest rate (first
row) is 7.5%.

6. I also set up cells that compute the total interest and the final
balance. With the values selected in #5, the total interest is under
13.5M, and the final balance is under -1.5M. The goal is for total
interest to be 15M and the final balance to be zero.

Now the "what if" begins.... Eventually, by setting the 1st-year rate
to 10% and the 2nd-year rate to 7.45%, the total interest becomes just
125 over 45M, and the final balance is 125 over zero. "Close enough
for government work" ;-).

Thus, one solution is to set the 1st-year rate to 10%, the 2nd-year
rate 7.45%, and the remainding years to 6.5% through 0.5%, decreasing
by 0.5% each year.

Hopefully that gives you a framework for finding other solutions that
might meet whatever contstraints you prefer.

PS: It might be possible to write a VBA macro that would apply adapted
linear programming techniques to the problem to help you find an
acceptable solution. IMHO, that goes far beyond the scope of free
advice ;-).

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default financial function ..please

Perfect .. I will not sleep until i try what you did.

Thanks a lot for your effort to me, God seeing your effort and will
thanks you more better than me.

good luck,
Bassem



wrote:
wrote:
I am in challenge with my boss if i can solve this


First, if you are dealing with millions of dollars (or any monetary
unit), you should be getting professional accounting help. You
certainly can afford it -- especially if you are the lender. But I
wonder if this is really a homework problem. It sounds a little too
contrived. No matter; we can have fun with it.

If a company borrow 30,000,000 $ from a bank.
It will pay monthly to the bank 3,000,000 $
The period of the loan is 15 years.
By the end of the loan he will found that have paid 45,000,000 $.


Those numbers do not make sense. I presume you mean to say: you will
make monthly payments totally 3M per year.

The interest rate is not fixed but getting decrease every year.
So he want to know how the interest rate will be in the first,
seconde and the third year ..until the year 15.

So, i need toi know please how to do it .. i want to understand to be
ready for future questions.


There are an infinite number of solutions. But you have already placed
some constraints on the solution. One way to find some solutions is to
place some additional constraints on the minimum and maximum interest
rates, then set up a spreadsheet and play "what if" games with the
numbers. For example....

If the monthly payments total 3M per year, the average monthly payment
must be 250,000 (3M/12). Thus, an annual nominal rate of 5.80% would
pay off the loan in 15 years, computed by:

=12*rate(15*12, -250000, 30e6)

If we decreased the interest rate from that point, the loan would be
paid off sooner. Therefo (1) the monthly payments must be allowed
to vary (but still total 3M per year); or (2) the annual total of
monthly payments must be allowed to be less than 3M sometimes; or (3)
the initial interest rate must be higher than 5.80%. And perhaps a
combination of all three are needed and acceptable.

Since you specified an annual total of 3M in monthly payments, I would
use a constant monthly payment of 250,000 and set the initial interest
rate higher than 5.80%. But how much higher depends on how much the
interest rate can decrease each year and how low you want it go. Both
constraints are unspecified.

I set up a spreadsheet with the following form:

1. One table with 181 rows, one for each month and one for the initial
balance. The columns are month number (0 through 180), interest, and
balance.

2. Another table with 15 rows. One column is the first month number
(1, 13, ..., 169) to which an interest rate applies; the other column
is the nominal annual interest rate (see #5 below).

3. The second column (interest) of the first table has the following
formula:

=prevBalance * vlookup(monthNumber, table2, 2) / 12

4. The third column (balance) of the first table is simply:

=prevBalance + interest - 250000

5. I arbitrarily set the second column (interest rate) of the second
table such that the lowest rate (last row) is 0.5%, and the interest
rate increases 0.5% in each row above. Thus, the highest rate (first
row) is 7.5%.

6. I also set up cells that compute the total interest and the final
balance. With the values selected in #5, the total interest is under
13.5M, and the final balance is under -1.5M. The goal is for total
interest to be 15M and the final balance to be zero.

Now the "what if" begins.... Eventually, by setting the 1st-year rate
to 10% and the 2nd-year rate to 7.45%, the total interest becomes just
125 over 45M, and the final balance is 125 over zero. "Close enough
for government work" ;-).

Thus, one solution is to set the 1st-year rate to 10%, the 2nd-year
rate 7.45%, and the remainding years to 6.5% through 0.5%, decreasing
by 0.5% each year.

Hopefully that gives you a framework for finding other solutions that
might meet whatever contstraints you prefer.

PS: It might be possible to write a VBA macro that would apply adapted
linear programming techniques to the problem to help you find an
acceptable solution. IMHO, that goes far beyond the scope of free
advice ;-).


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
VBA Monty Excel Worksheet Functions 2 January 30th 06 02:37 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM


All times are GMT +1. The time now is 12:55 PM.

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"