Spreadsheet: 878789d1726062930-excel-dynamic-array-formula-refinement-sample-excel-template
Sheet: Sheet1

 | bond | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29
 | urc sample | 4.419406196137288 | -0.08245900455777111 | -0.2092675950489187 | 0.2030799966299202 | -16.02505987183875 | -5.489053896451603 | -3.679132061264003 | -6.035071896495829 | -10.73538748107739 | 2.529514457335868 | -0.09114102148599201 | 0.2565849127627899 | -0.04397749582489219 | -1.508205709778629 | 0.6478684883068873 | 0.3369110732166787 | 0.2341927429827857 | -7.776108886575315 | -5.72425288782471 | -7.444758361451697 | 0.03769909894089496 | 0.2150879323154715 | -1.693931723845829 | 1.389511780858342 | 0.382115887866787 | -0.07753059753849456 | -0.006432388444436388 | 1.066293405594237 | 0.5413587681283403
 | BV% sample | 0.08354255044090034 | 0.008648251847278618 | 0.006521105707554779 | 0.006741483854925853 | 0.1652584093000444 | 0.02111838779302392 | 0.01809635882895125 | 0.01944194612534004 | 0.03892222647581933 | 0.04030669330974127 | 0.04780329188854381 | 0.0217059973354019 | 0.02887017594944587 | 0.03945273690381264 | 0.03010098596817795 | 0.006612463483363988 | 0.01115685236770108 | 0.0309283802107143 | 0.02871798543637476 | 0.02760005798941452 | 0.006974783189590412 | 0.009499715025350476 | 0.08182757890892559 | 0.02323230345782063 | 0.02549061770515725 | 0.01574023061543345 | 0.002503364357099702 | 0.06602748577884524 | 0.08715757974524664
 | BV% |  | 0.008648251847278618 | 0.006521105707554779 | 0.006741483854925853 | 0.1652584093000444 | 0.02111838779302392 | 0.01809635882895125 | 0.01944194612534004 | 0.03892222647581933 | 0.04030669330974127 | 0.04780329188854381 | 0.0217059973354019 | 0.02887017594944587 | 0.03945273690381264 | 0.03010098596817795 | 0.006612463483363988 | 0.01115685236770108 | 0.0309283802107143 | 0.02871798543637476 | 0.02760005798941452 | 0.006974783189590412 | 0.009499715025350476 | 0.08182757890892559 | 0.02323230345782063 | 0.02549061770515725 | 0.01574023061543345 | 0.002503364357099702 | 0.06602748577884524 | 0.08715757974524664
 | urc |  | -0.08245900455777111 | -0.2092675950489187 | 0.2030799966299202 | -16.02505987183875 | -5.489053896451603 | -3.679132061264003 | -6.035071896495829 | -10.73538748107739 | 2.529514457335868 | -0.09114102148599201 | 0.2565849127627899 | -0.04397749582489219 | -1.508205709778629 | 0.6478684883068873 | 0.3369110732166787 | 0.2341927429827857 | -7.776108886575315 | -5.72425288782471 | -7.444758361451697 | 0.03769909894089496 | 0.2150879323154715 | -1.693931723845829 | 1.389511780858342 | 0.382115887866787 | -0.07753059753849456 | -0.006432388444436388 | 1.066293405594237 | 0.5413587681283403
 | ind |  | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29
 | urc sorted inc |  | -10.73538748107739 | -7.776108886575315 | -7.444758361451697 | -6.035071896495829 | -5.72425288782471 | -5.489053896451603 | -3.679132061264003 | -1.693931723845829 | -1.508205709778629 | -0.2092675950489187 | -0.09114102148599201 | -0.08245900455777111 | -0.07753059753849456 | -0.04397749582489219 | -0.006432388444436388 | 0.03769909894089496 | 0.2030799966299202 | 0.2150879323154715 | 0.2341927429827857 | 0.2565849127627899 | 0.3369110732166787 | 0.382115887866787 | 0.5413587681283403 | 0.6478684883068873 | 1.066293405594237 | 1.389511780858342 | 2.529514457335868 | 4.419406196137288
 | cum urc inc |  | -26.76044735291614 | -34.53655623949146 | -41.98131460094316 | -48.01638649743899 | -53.7406393852637 | -59.2296932817153 | -62.9088253429793 | -64.60275706682512 | -66.11096277660374 | -66.32023037165266 | -66.41137139313865 | -66.49383039769643 | -66.57136099523493 | -66.61533849105982 | -66.62177087950425 | -66.58407178056336 | -66.38099178393344 | -66.16590385161797 | -65.93171110863518 | -65.6751261958724 | -65.33821512265571 | -64.95609923478892 | -64.41474046666059 | -63.76687197835371 | -62.70057857275947 | -61.31106679190113 | -58.78155233456526 | -54.36214613842797
 | cum urc reverse |  | -58.78155233456526 | -61.31106679190113 | -62.70057857275947 | -63.76687197835371 | -64.41474046666059 | -64.95609923478892 | -65.33821512265571 | -65.6751261958724 | -65.93171110863518 | -66.16590385161797 | -66.38099178393344 | -66.58407178056336 | -66.62177087950425 | -66.61533849105982 | -66.57136099523493 | -66.49383039769643 | -66.41137139313865 | -66.32023037165266 | -66.11096277660374 | -64.60275706682512 | -62.9088253429793 | -59.2296932817153 | -53.7406393852637 | -48.01638649743899 | -41.98131460094316 | -34.53655623949146 | -26.76044735291614 | -16.02505987183875
 | bv% sorted |  | 0.03892222647581933 | 0.0309283802107143 | 0.02760005798941452 | 0.01944194612534004 | 0.02871798543637476 | 0.02111838779302392 | 0.01809635882895125 | 0.08182757890892559 | 0.03945273690381264 | 0.006521105707554779 | 0.04780329188854381 | 0.008648251847278618 | 0.01574023061543345 | 0.02887017594944587 | 0.002503364357099702 | 0.006974783189590412 | 0.006741483854925853 | 0.009499715025350476 | 0.01115685236770108 | 0.0217059973354019 | 0.006612463483363988 | 0.02549061770515725 | 0.08715757974524664 | 0.03010098596817795 | 0.06602748577884524 | 0.02323230345782063 | 0.04030669330974127 | 0.08354255044090034
 | cum bv% inc |  | 0.2041806357758638 | 0.235109015986578 | 0.2627090739759926 | 0.2821510201013326 | 0.3108690055377074 | 0.3319873933307314 | 0.3500837521596826 | 0.4319113310686081 | 0.4713640679724208 | 0.4778851736799755 | 0.5256884655685193 | 0.5343367174157979 | 0.5500769480312314 | 0.5789471239806773 | 0.581450488337777 | 0.5884252715273675 | 0.5951667553822934 | 0.6046664704076439 | 0.6158233227753449 | 0.6375293201107468 | 0.6441417835941108 | 0.669632401299268 | 0.7567899810445147 | 0.7868909670126927 | 0.8529184527915379 | 0.8761507562493586 | 0.9164574495590998 | 1
 | cum urc reverse |  | 0.7958193642241362 | 0.7648909840134219 | 0.7372909260240075 | 0.7178489798986674 | 0.6891309944622925 | 0.6680126066692686 | 0.6499162478403174 | 0.5680886689313919 | 0.5286359320275793 | 0.5221148263200245 | 0.4743115344314807 | 0.4656632825842021 | 0.4499230519687686 | 0.4210528760193227 | 0.418549511662223 | 0.4115747284726325 | 0.4048332446177066 | 0.3953335295923561 | 0.3841766772246551 | 0.3624706798892532 | 0.3558582164058892 | 0.330367598700732 | 0.2432100189554853 | 0.2131090329873073 | 0.1470815472084621 | 0.1238492437506414 | 0.0835425504409002 | 0
 | urc/BV |  | -275.8163767364854 | -251.4230888781396 | -269.737055056442 | -310.4150097726019 | -199.3264082018183 | -259.9182262513815 | -203.3078640868895 | -20.70123235261771 | -38.22816433383811 | -32.09081472279765 | -1.90658462807316 | -9.534759858284982 | -4.925632885103668 | -1.523284648555676 | -2.569497494918676 | 5.40505674744978 | 30.12393131840464 | 22.64151416558269 | 20.99093321883242 | 11.8209225219201 | 50.95091626052812 | 14.99045226312728 | 6.211264352574736 | 21.5231650216308 | 16.14923532247945 | 59.8094710402293 | 62.75668504725834 | 52.90006317515604
 | bv% bucket |  | 0.01 | 0.02 | 0.03 | 0.04 | 0.05 | 0.06 | 0.07 | 0.08 | 0.09 | 0.1 | 0.11 | 0.12 | 0.13 | 0.14 | 0.15 | 0.16 | 0.17 | 0.18 | 0.19 | 0.2 | 0.21 | 0.22 | 0.23 | 0.24 | 0.25 | 0.26 | 0.27 | 0.28 | 0.29 | 0.3 | 0.31 | 0.32 | 0.33 | 0.34 | 0.35 | 0.36 | 0.37 | 0.38 | 0.39 | 0.4 | 0.41 | 0.42 | 0.43 | 0.44 | 0.45 | 0.46 | 0.47 | 0.48 | 0.49 | 0.5 | 0.51 | 0.52 | 0.53 | 0.54 | 0.55 | 0.56 | 0.57 | 0.58 | 0.59 | 0.6 | 0.61 | 0.62 | 0.63 | 0.64 | 0.65 | 0.66 | 0.67 | 0.68 | 0.69 | 0.7 | 0.71 | 0.72 | 0.73 | 0.74 | 0.75 | 0.76 | 0.77 | 0.78 | 0.79 | 0.8 | 0.81 | 0.82 | 0.83 | 0.84 | 0.85 | 0.86 | 0.87 | 0.88 | 0.89 | 0.9 | 0.91 | 0.92 | 0.93 | 0.94 | 0.95 | 0.96 | 0.97 | 0.98 | 0.99 | 1
 | sell losses |  | -0.969697090738876 | -1.939394181477752 | -2.909091272216628 | -3.878788362955504 | -4.84848545369438 | -5.818182544433256 | -6.787879635172133 | -7.757576725911008 | -8.727273816649884 | -9.69697090738876 | -10.66666799812764 | -11.63636508886651 | -12.60606217960539 | -13.57575927034427 | -14.54545636108314 | -15.51515345182202 | -46.88878404520253 | -49.64694781256738 | -52.40511157993223 | -55.16327534729709 | -52.79884866440932 | -55.31307955319072 | -57.82731044197212 | -64.73689321354608 | -67.43426376411051 | -70.13163431467494 | -83.81205263860252 | -86.91620273632854 | -57.80465837852731 | -59.79792246054549 | -61.79118654256368 | -83.17383240044208 | -85.7730146629559 | -69.12467378954243 | -71.1577524304113 | -7.452443646942375 | -7.659455970468552 | -7.866468293994729 | -8.073480617520907 | -8.280492941047084 | -8.48750526457326 | -8.694517588099437 | -8.901529911625614 | -16.82039230688877 | -17.20267395022715 | -17.58495559356553 | -17.96723723690391 | -0.9151606214751169 | -0.9342264677558485 | -0.9532923140365801 | -0.9723581603173117 | -0.9914240065980433 | -5.053422724891041 | -2.659841757955981 | -2.709098086807018 | -0.8530394031911785 | -0.8682722496767352 | -1.490308547052832 | 17.77311947785874 | 13.58490849934961 | 12.80446926348777 | 7.328971963590459 | 7.447181188809661 | 32.608586406738 | 9.743793971032735 | 9.893698493664008 | 4.161547116225073 | 4.22365975975082 | 4.285772403276567 | 4.347885046802315 | 4.409997690328062 | 4.472110333853809 | 4.534222977379557 | 4.596335620905304 | 4.658448264431051 | 16.35760541643941 | 16.57283706665572 | 16.78806871687203 | 12.75789590475877 | 12.91938825798356 | 13.08088061120836 | 13.24237296443315 | 13.40386531765794 | 13.56535767088274 | 13.72685002410753 | 51.4361450945972 | 52.03423980499949 | 55.22588284158734 | 55.85344969205992 | 56.4810165425325 | 57.10858339300509 | 48.66805812114356 | 49.19705875289512 | 49.72605938464667 | 50.25506001639824 | 50.7840606481498 | 51.31306127990136 | 51.84206191165292 | 52.37106254340448 | 52.90006317515604
 | new formula
 | my excel formula is a dynamic array formula.
 | C7# is sorted unrealised capital (URC) by bond in cells AG17:BI17 (29 bonds).
 | C8# is cumulative URC.
 | C10# is book value or BV percentage (BV%) - so BV of the bond divided by total BV of the portfolio.
 | C11# is cumulative BV%.
 | C13# is URC/BV%.
 | C17:CY17 is book value % buckets starting from 0% going to 100% in 1% increments.
 | I need a formula in cell AG28 which will evaluate realised capital on sale of bond BV% in buckets given in C17:CY17
 | Current formula I have is:
 | =C17# * INDEX(C13#, IFERROR(MATCH(C17#, C11#),0)+1)
 | which gives the realised capital but incorrectly applies the urc/bv% of the later bonds to the sold BV%. So for example, for the 20% bucket, if bond 1 has 15% BV% and bond 2 has 5% BV%, then it calculates realised capital % as URC/BV% of bond 2 x 20% BV% bucket.
 | I want it to do URC/BV% of bond 1 x 15% + URC/BV% of bond 2 x 5%.
 | The formula needs to be dynamic array as number of bonds vary by portfolio I run. Please suggest a refinement that I struggling to come up with.