Conas meánráta fáis bliantúil / cumaisc in Excel a ríomh?

Tá an t-alt seo ag caint ar bhealaí chun an Meánráta Fáis Bliantúil (AAGR) agus an Ráta Fáis Bliantúil Comhdhéanta (CAGR) a ríomh in Excel.

Ríomh ráta fáis bliantúil cumaisc in Excel
Ríomh ráta fáis bliantúil cumaisc le feidhm XIRR in Excel
Ríomh an meánráta fáis bliantúil in Excel

Ríomh an Ráta Fáis Bliantúil Comhdhéanta in Excel

Chun an Ráta Fáis Bliantúil Comhdhéanta a ríomh in Excel, tá foirmle bhunúsach ann = ((Luach Deiridh / Luach Tosaigh) ^ (1 / Tréimhsí) -1. Agus is féidir linn an fhoirmle seo a chur i bhfeidhm go héasca mar a leanas:

1. Roghnaigh cill bhán, mar shampla Cill E3, cuir isteach an fhoirmle thíos inti, agus brúigh an Iontráil eochair. Féach an pictiúr:

=(C12/C3)^(1/(10-1))-1

Nóta: San fhoirmle thuas, is í C12 an chill le luach deiridh, is í C3 an chill le luach tosaigh, is é 10-1 an tréimhse idir luach tosaigh agus luach deiridh, agus is féidir leat iad a athrú bunaithe ar do chuid riachtanas.

2. I roinnt cásanna, ní fhéadfaidh toradh an ríofa formáidiú mar chéatadán. Coinnigh toradh an ríofa le do thoil, cliceáil an Stíl Céatadáin cnaipe ar an Baile cluaisín chun an uimhir a athrú go formáid chéatadáin, agus ansin na háiteanna deachúlacha a athrú trí chliceáil ar an Deachúil a Mhéadú cnaipe or Laghdaigh Deachúil cnaipe . Féach griangraf:

Ríomh ráta fáis bliantúil cumaisc le feidhm XIRR in Excel

I ndáiríre, is féidir le feidhm XIRR cabhrú linn an Ráta Fáis Bliantúil Comhdhéanta in Excel a ríomh go héasca, ach éilíonn sé ort tábla nua a chruthú leis an luach tosaigh agus an luach deiridh.

1. Cruthaigh tábla nua leis an luach tosaigh agus an luach deiridh mar a thaispeántar an chéad ghrianghraf scáileáin seo a leanas:

Nóta: I gCill F3 iontráil = C3, i gCill G3 cuir isteach = B3, i gCill F4 cuir isteach = -C12, agus i gCill G4 cuir isteach = B12, nó is féidir leat do chuid sonraí bunaidh a iontráil sa tábla seo go díreach. Dála an scéil, ní mór duit lúide a chur leis roimh an Luach Deiridh.

2. Roghnaigh cill bhán faoin tábla seo, cuir isteach an fhoirmle thíos inti, agus brúigh an Iontráil eochair.

= XIRR (F3: F4, G3: G4)

3. Chun an toradh a athrú go formáid chéatadáin, roghnaigh an Cill leis an bhfeidhm XIRR seo, cliceáil an Stíl Céatadáin cnaipe ar an Baile cluaisín, agus ansin athraigh a áiteanna deachúla trí chliceáil ar an Deachúil a Mhéadú cnaipe or Laghdaigh Deachúil cnaipe . Féach griangraf:

Sábháil an tábla CAGR go tapa mar mhion-theimpléad, agus athúsáid le cliceáil amháin sa todhchaí

Caithfidh sé a bheith an-tedious cealla a tharchur agus foirmlí a chur i bhfeidhm chun na meáin a ríomh gach uair. Soláthraíonn Kutools for Excel sreabhadh oibre gleoite de AutoText fóntais chun an raon a shábháil mar iontráil AutoText, ar féidir leis fanacht sna formáidí agus na foirmlí cille sa raon. Agus ansin athúsáidfidh tú an raon seo gan ach cliceáil amháin.

Triail In Aisce 30 Lá Anois!

Ceannaigh Anois!

autotext doc cagr

Ríomh Meánráta Fáis Bliantúil in Excel

Chun an Meánráta Fáis Bliantúil a ríomh ar fheabhas, de ghnáth ní mór dúinn rátaí fáis bliantúla gach bliana a ríomh leis an bhfoirmle = (Luach Críochnaithe - Luach Tosaigh) / Luach Tosaigh, agus ansin na rátaí fáis bliantúla seo a mheánú. Is féidir leat a dhéanamh mar a leanas:

1. Seachas an tábla bunaidh, cuir isteach an fhoirmle thíos sa Chill bán C3 agus, agus ansin tarraing an Láimhseáil Líon isteach chuig Raon C3: C11.

= (C4-C3) / C3

2. Roghnaigh Raon D4: D12, cliceáil an Stíl Céatadáin cnaipe ar an Baile cluaisín, agus ansin athraigh a áiteanna deachúla trí chliceáil ar an Deachúil a Mhéadú cnaipe or Laghdaigh Deachúil cnaipe . Féach an pictiúr:

3. Meánráta an fháis bhliantúil ar fad agus an fhoirmle seo a iontráil i gCill F4, agus brúigh an Iontráil eochair.

= FÍOR (D4: D12)

Go dtí seo, rinneadh Meánráta Fáis Bliantúil a ríomh agus a thaispeáint i gCill C12.

Taispeántas: ríomh meánráta fáis bliantúil / cumaisc in Excel

Kutools le haghaidh Excel Cuimsíonn sé níos mó ná 300 uirlis áisiúil le haghaidh Excel, saor in aisce le triail gan teorainn i 30 lá. Íoslódáil agus Triail In Aisce Anois!

Earraí gaolmhara:

Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

22 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Jamal M.El khoga · 1 years ago
Thank u very much for important scientific information
- Reply
To post as a guest, your comment is unpublished.
Prashant Landge · 1 years ago
How to calculate average of percentage CAGR Return ?
Example :

28.6%
34.9%
25.5%
-2.8%
16.0%
- Reply
To post as a guest, your comment is unpublished.
triple triple 6997r6 · 1 years ago
@Manoj ga ga ga gu gu gu gu gu gay
- Reply
To post as a guest, your comment is unpublished.
John Taylor · 1 years ago
You can also just use the RATE() formula with setting PMT to 0. RATE(9,0,-549,987) = 6.73%. Built right into Excel already.
- Reply
To post as a guest, your comment is unpublished.
HB · 1 years ago
@Daniel thank you. Strange only two others seem to have noticed this. I'd like to know why doesn't the formula just say to put in the actual number of periods, such as the 9 periods shown instead of ten minus one? Actually gave the correct formula n=number of periods) which is smart, because then eveeryone can use it for say 4/1/xx to 9/31/xx and actually list the number of periods in years with a decimal to represent the part of the year related to thte number of months between the beginning of April to the end of Sept
- Reply
To post as a guest, your comment is unpublished.
HB · 1 years ago
@pp I agree, there's only 9 periods from 1/1/11 to 1/1/20. Ten periods would be 1/1/11 to 12/31/20. Please explain
- Reply
To post as a guest, your comment is unpublished.
pp · 2 years ago
Your period is wrong in CAGR formula ?
- Reply
To post as a guest, your comment is unpublished.
DQ · 2 years ago
Hi - I'm trying to work backwards to find the highest price I can buy a share at when I have a total expected return. Are you able to please help me by reverse engineering the formula to work this out. Using your example - I'm trying to work out what the "3" should be 2.43443 =(3200/x)^(1/(40-8))-1
- Reply
To post as a guest, your comment is unpublished.
kelly.extendoffice@gmail.com · 3 years ago
@Robert L. Hi Robert,
You can use this formula = (Ending Value - Beginning Value) / Beginning Value to calculate the growth rate of each year, and then compare those growth rates one by one.
- Reply
To post as a guest, your comment is unpublished.
Robert L. · 3 years ago
@Robert L. FY12 - 2653
FY13 - 2848
FY14 - 2871
FY15 - 2925
FY16 - 2685
FY17 - 2923
FY18 - 3000
- Reply
To post as a guest, your comment is unpublished.
Robert L. · 3 years ago
Hi,

I have 7 fiscal years of foot traffic data for a retail store:

FY12 FY13 FY14 FY15 FY16 FY17 FY18
2653 2848 2871 2925 2685 2923 3000

My question is: while traffic is increasing, is it increasing at a decreasing rate? Is the growth slowing?

Your first example "(B11/B2)^(1/(10-1))-1" takes the end value and beginning value to get the CAGR. The part I don't understand is that, what about the values in the middle? How does only taking the end and beginning value determine the growth rate accurately? Is there another method where it takes all the fiscal year values into account?
- Reply
To post as a guest, your comment is unpublished.
kelly.extendoffice@gmail.com · 3 years ago
@Wayne Hurt Hi Wayne,
You can calculate the Compound Annual Growth Rate with the second method:
=(3200/3)^(1/(40-8))-1
- Reply
To post as a guest, your comment is unpublished.
Wayne Hurt · 3 years ago
Can someone help with this problem using excel?

The following data show average growth of the human embryo prior to birth.
EMBRYO AGE IN WEEKS WEIGHT IN GRAMS
8 3
12 36
20 330
28 1000
36 2400
40 3200

a). Find the quadratic function of “best fit” for this data. Write this function in standard form: f(x) = ax2 + bx + c.
b). Make a sketch of the scatter plot and the parabola. Plot the quadratic function found above the Y = menu.
c). According to your model, what would a 32-week embryo weigh?
d). According to your model, what week would an embryo weigh 3000 grams?
e). Could the model be used for weight of an embryo for any number of weeks age (such as 100 or 200)? Explain
- Reply
To post as a guest, your comment is unpublished.
McKing · 4 years ago
There is a new tool that will fit to your planning software.
MS Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS.
It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
Thus, it can do Bookkeeping/Accounting, Budgeting (Existing Year and Long Term) and Data Analysis.
- Reply
To post as a guest, your comment is unpublished.
Sergio · 4 years ago
@Shailender The first formula you are using is the correct one!
- Reply
To post as a guest, your comment is unpublished.
Shailender · 4 years ago
In the CAGR formula, why we are using -1 at the end.
I am using two formulaes 1) (I5/I4)^(1/(25-1))-1
2)(I5/I4)^(1/(25)-1) which one is correct..?
Please help me out on this thank you.
- Reply
To post as a guest, your comment is unpublished.
Daniel · 4 years ago
The formula is actually CORRECT, but the explanation is incorrect. There are 10 dates that represent 9 periods. It should say that "n-1" means "dates-1", not "periods - 1".

You can verify it yourself by increasing each year by this example's n-1 CAGR and you will get the final result.
- Reply
To post as a guest, your comment is unpublished.
Daniel · 4 years ago
The original formula is CORRECT, but its explanation is wrong; It confuses the number of dates shown with the number of periods. In the example, there are ten dates listed but they extend over only 9 years (periods). That is why there is a minus 1.Try it yourself by calculating each year's increase at the CAGR of 6.5257% and you will get the correct ending answer.
- Reply
To post as a guest, your comment is unpublished.
Andrew · 4 years ago
As noted above, this formula is INCORRECT and misleading (especially since it's the first Google result). Please correct!
- Reply
To post as a guest, your comment is unpublished.
ramsy foss · 4 years ago
I need the amount $20,000 @ 8% & 10% would be after 30 years compounded growth please. Thank you, Ramsy
- Reply
To post as a guest, your comment is unpublished.
Dan · 4 years ago
Agree with MANOJ - the formula is wrong. it is not n-1
- Reply
To post as a guest, your comment is unpublished.
Manoj · 5 years ago
Hi Bro,

Your Formula for CAGR: [quote][b]=((End Value/Start Value)^(1/(Periods - 1)) -1[/b] [/quote] highlighted in blue is incorrect.

The correct CAGR formula is:

= ((FV/PV)^(1/n)) - 1

Where,
FV: Future Value
PV: Present Value
n: Number of years
- Reply

Logáil isteach do chuntas

Athshocraigh Pasfhocal