Percentage Calculations - Again

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
298
Office Version
  1. 365
Hi
I am having trouble with this spreadsheet. I am running Windows XP and Excel 2003.

Thanks

Steve
Book2.xls
ABCDEFGH
4VIEW 1Quarterly RateBase1st Quarter2nd Quarter3rd Quarter4th QuarterEquivalent Annual Escalation
54.663%11.0471.0951.1471.20020.00%
60.0470.0490.0510.0530.056
7
8Input quarterly rate into B5 and the result is shown in H5 as the equivalent annual rate
9
10
11
12VIEW 2Quarterly RateBase1st Quarter2nd Quarter3rd Quarter4th QuarterEquivalent Annual Escalation
134.225%11.0421.0861.1321.18020.00%
140.0420.0440.0460.0480.050
15
16Input annual rate into H13 and the result is shown in B13 as the equivalent quarterly rate
17Require cells C13 to G14 to have formulas which give the answer in B13
18Answer should show 4.663% in B13 if 20% is inputted in to H13
19Only cells available for inputs are B5 and H13
Sheet1 (2)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Steve

If you have your annual equivalent, then surely your quarterly interest rate will be:

=(1+AR)^(days/year)

or in this case:

=(1+20%)^(1/4).

How are you determining it?

Richard
 

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
298
Office Version
  1. 365
4.663% is the correct answer if it is used as a quarterly escalation. This will equate to 20% at the end of four quarters. Compounding interest is not as simple as multiplying 4.663 x 4. Obviously the answer would be 5% and not 4.663%.

In view 1, I am able to input the quarterly escalation and see what that translates to on an annual basis. What I want to do in view 2 is to input an annual rate and see what that equates to on a quarterly basis.

SH
 

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
298
Office Version
  1. 365

ADVERTISEMENT

I want the spreadsheet to give the same representation as in view 1. Eg: have each quarter change accordingly but achieve this by inputting the annual rate, not the quarterly rate. I want the quarterly rate to be calculated by way of formula.

SH
 

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
298
Office Version
  1. 365
Hi Richard

Thank you very much - you are absolutely BRILLIANT!

It works perfectly and is exactly what I wanted.

All the best and thanks again.

Steve
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top