Excel Busn Math 30: Rate Of Change Business Problems

I need an excel formula to calculate the negative nominal rate of return given the negative effective rate and the number of compounding periods per year. Excel's NOMINAL and NPER functions do not work when a negative rate assumption is used resulting in a #NUM! error. The context for this is that I am using MS Excel to to investment analysis. I want to analyse the resulting value of an investment account balance when there is a negative effective rate and corresponding nominal rate of return (mimicing a declining value in a particular investment/account due to negative annual return on investment expressed as a negative annual effective rate of return but that is compounding monthly - and hence the need for the nominal rate and compounding periods). I need to use a calcualtion in Excel that gives me the negative nominal rate of return equivalent to the negative effective rate of return assumtion.

Please help and thank you in advance if you can solve this!

Hi,

I would be grateful if somebody can help me with this. I have two slightly different formulas that I am using to calculate the number of business days between two dates. I am not using network days because the analysis toolpak isn't an option (the spreadsheet is viewed business wide and we are outputting to a web page). I need to build into these formulas a way of excluding non-working holidays from the calculation (from a list hidden elsewhere in the sheet) but I'm a novice and I don't have any idea where to insert it in the formula without causing errors in other cells.

Formula A is in column D and calculates the business days between a date in the past (column B) and today's date (column C). I added a '-1' at the end because the formula was starting the counting from the date in column B and not the day AFTER that I wanted it to (e.g. the difference between 01/09/2009 and 02/09/2009 would show as 2 working days instead of 1 without the '-1').

Formula A:
=(INT(C4/7)-INT(B4/7))*5+MAX(0,MOD(C4,7)-1)-MAX(0,MOD(B4,7)-1)

Formula B is in column I and calculates the business days between the dates in columns F (earliest date) and G (latest date). I want the cell to be blank if column G is empty (because of a dependant formula in another cell*) and again I have added the '-1' so that counting begins the day AFTER the first date. I have also added an element that subtracts the number (if there is one) in column H from the overall calculation (column H represents a number of days manually entered that we want excluded from the calculation).

Formula B:
=IF(ISBLANK(G5),"",(INT(G5/7)-INT(F5/7))*5+MAX(0,MOD(G5,7)-1)-MAX(0,MOD(F5,7)-1)-H5)

*The dependent formula that I mentioned above is in column J:
=IF(I5="","N/A",IF(I5-H5

I want to calculate the survival rate/failure rate for a product. Usually I use weibull distribution (linear regression followed by the 'WEIBULL' function), but unfortunately the data I have simply won't work with my current methods.

I have 10 different groups of widgets.
Group 1 widgets were installed 1 year ago. A certain percentage of those widgets are failed.
Group 2 widgets were installed 2 years ago. A certain percentage of those widgets are failed.
...
Group 10 widgets were installed 10 years ago. A certain percentage of those widgets are failed.

There are far fewer 10-year widgets than 1-year widgets out there.

The catch is that I don't know if the group 1 widgets will survive year 2, or if year 2 widgets will survive year 3, etc.

I want to create a nice curve that tells me (approximately) how many years the average widget in my sample dies 50% of the time. Unfortunately, there doesn't seem to be any ready-made formula in Excel for this. So I turn to you, brave Excel Board Members, to help me save myself from Math.

Please?

Hi there! I need a formula to calculate the negative nominal rate of return given the negative effective rate and the number of compounding periods per year . Excel's NOMINAL and NPER functions do not work when a negative rate assumption is used resulting in a #NUM! error. The context for this is that I am using MS Excel to to investment analysis. I want to analyse the resulting value of an investment account balance when there is a negative effective rate and corresponding nominal rate of return (mimicing a declining value in a particular investment/account due to negative annual return on investment expressed as a negative annual effective rate of return but that is compounding monthly - and hence the need for the nominal rate and compounding periods). I need to use a calcualtion in Excel that gives me the negative nominal rate of return equivalent to the negative effective rate of return assumtion.

Please help and thank you in advance if you can solve this!

Hi,
I have wave data and need to extract out of a column the greatest rate of change i.e. start from a1 if the numbers are going down find the first number that starts going upward...and put in column b...then find the next first number that is going down and put in column b....and so on and so on all the way down the column.

Thanks in advance for any help.
M

After I cleaned up the data your formula worked like a charm...THANK YOU!!!

Hello Everyone,

I need your help on the following task.

As you can see in the attached sample file, I have different currencies, that all need to be converted to USD.
The exchange rate are displayed on sheet 2.
Currently the formula just picks all values without considering the exchange rate to USD. What I need to have, is a formula which, based on the currency, picks the correct exchange rate and outputs USD.
Apart from that, another thing the formula should consider is the value of G1 in sheet 2. With G1, the user can later define a specific type to be calculated by simply pressing down the button and chose either FG or RM.

The formula needs to:
Find out what currency the value is in and automatically convert it to USD via the exchange rate values.
It also needs to consider the value of G1 in sheet 2 in order to filter out the other values. If, for example G1 is FG, then only FG should be taken into the process and therefore only values where type FG is marked.

The current formula, as you can see in the workbook is:
Code:

=SUMPRODUCT((JAN09!$A$2:$A$100=A4)*(JAN09!$C$2:$C$100))

This of course does not take any of the above into consideration.
I am quite new to Excel, and I don't even know if something like this is possible. I would be grateful for any help - be it advise or solutions.
Thanks a lot!

I am attempting to use Excel to track my ongoing weight loss. I have an artificial heart valve and my cardiologist has advised me not to lose more than 1 pound a week lest I put strain on my valve.

In my spreadsheet:

Column A is dates. (I put the date I weigh myself here.)
Column B is my weight in pounds.

I would like to know my average weekly change in weight (in pounds).

Any help you can provide would be greatly appreciated.

My friend made a program in Excel for calculating interest on loan, Since interest on loan is not constant, the date for the change of interest rate has to be manually inserted to get the correct result.

Now I wanted to add two more functions in this program:

1- This program should account for itself the dates when new interest rate comes into effect.

and

2- It should also add same amount of Loan periodically, i.e., annually, showing the dates besides the dates when interest rate changes.

I have tried my best to include these features but not successful. Help from forum members will be highly appreciated.


Here is the program for your ready reference.

Regards

I have a spreadsheet that has been "gifted" to me to calculate commissions for our sales folks. It looks like this:

Commissions for the month of: October Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD Commission John Smith 75000 61895 98105 76000 59000 110000 230000 140000 100000 100000 1050000


Our sales staff are paid 1% commissions until they reach a YTD total of $1M in sales. Once they reach $1M in sales, they are paid 1.5%.

Right now the formula is a simple IF statement that looks at the YTD total.

I have a number of problems:
Each month, when I input the new sales data, I have to change the formula in the "commissions" column to reference the right month's sales to calculate commissions. In months that they reach the $1M threshold, I am having to put in a new formula that calculates 1% commission on the bit of sales before $1M and 1.5% on the bit that is over $1M. Then in the next month, I change it back to the original formula. Can anyone think of an easier way to calculate this that would have the commission column reference the column with the current month? AND a way to calculate mid-month rate changes?

Please let me know if anything is unclear or if you need more information.

Thanks in advance!