Computing investment return formula

P

polarwoc

New Member
Joined
Apr 3, 2023
Messages
9
Office Version
  1. 2021
Platform
  1. Windows
Hi Guys,

This question probably has more to do with Finance formula rather than excel itself but any assistance would be appreciated. I have a sheet that lists investments made in a 10 year window where monthly investments were made at a prevailing rate and regular 75000 amounts have been withdrawn each month.

On the top right, where it shows Return %, the result of the computed formula (shown in Red BG) should show 17.09%. Obviously, I am unable to figure out the right formula to compute the return, hence I am getting 257%.

Could someone correct me so the formula computes return and it shows 17.09%?

Many thanks.

MF SWP Calculator
SchemeWithdrawal PeriodNo of Monthly InstallmentsTotal Withdrawal AmountCurrent Value (As on 3/7/23)Return(%)17.09
ICICI Pru Equity & Debt INF109K01Y071-8-2013 to29-7-2023120900000026719561.27257.1956127-0.82%
NAV DateNAVUnitsCumulative UnitsCash FlowNet AmountCapital Gain/LossNo of Days (Invested)Current ValueCapital Gain/Loss in 1 YearCapital Gain ExemptionActual Capital GainGain - Net of all taxesEffective Tax
01-07-201355.51180147.7180147.721110000000100000000010000000
01-08-201354.031388.118178759.6034-750009925000-2054.414214319658381.373
02-09-201353.141411.366177348.2372-750009850000-3344.9379639424285.326
01-10-201355.751345.291176002.9457-750009775000322.8699552929812164.225
01-11-201359.451261.564174741.3814-7500097000004970.56349912310388375.12
02-12-201359.931251.46173489.9214-7500096250005531.45336215410397250.99
01-01-201462.041208.897172281.0239-7500095500007894.1005818410688314.72
03-02-201460.191246.054171034.9697-7500094750005831.53347721710294594.83
03-03-201462.421201.538169833.4317-7500094000008302.62736324510601002.81
01-04-201466.051135.503168697.9283-75000932500011968.205927411142498.17
02-05-201467.121117.402167580.5267-75000925000012973.0333730511248004.95
02-06-201473.781016.536166563.991-75000917500018572.1062633612289091.2670967.14166010645.07125889354.92881.182785694
01-07-201478.17959.4474165604.5437-75000910000021741.0771436512945307.18
01-08-201477.95962.1552164642.3884-75000902500021590.7633139612833874.18
01-09-201482.16912.853163729.5355-75000895000024327.5316542713452018.63
01-10-201483.46898.6341162830.9014-75000887500025116.8224345713589867.03
03-11-201486.80864.0553161966.8461-75000880000027036.2903249014058722.24
01-12-201489.01842.602161124.2441-75000872500028227.1654951814341668.97
01-01-201590.40829.646160294.5981-75000865000028946.3495654914490631.67
02-02-201592.99806.5383159488.0598-75000857500030229.0568958114830794.68
02-03-201594.54793.315158694.7448-75000850000030963.0844160915003001.17
01-04-201592.31812.4797157882.2651-75000842500029899.2525263914574111.89
04-05-201590.84825.6275157056.6376-75000835000029169.4187667214267024.96
01-06-201592.20813.449156243.1886-75000827500029845.4446970014405621.99327092.257210000022709.22572877290.77432.523247302
01-07-201591.94815.7494155427.4392-75000820000029717.7507173014289998.76
03-08-201594.71791.891154635.5482-75000812500031042.128676314645532.77
01-09-201589.01842.602153792.9462-75000805000028227.1654979213689110.14
01-10-201590.40829.646152963.3002-75000797500028946.3495682213827882.34
02-11-201592.08814.5091152148.7911-75000790000029786.5986185414009860.68
01-12-201592.40811.6883151337.1027-75000782500029943.1818288313983548.29
01-01-201692.37811.9519150525.1508-75000775000029928.5482391413904008.18
01-02-201687.83853.9223149671.2285-75000767500027598.7703594513145624
01-03-201684.58886.7345148784.494-75000760000025777.3705497412584192.5
01-04-201689.46838.3635147946.1305-75000752500028462.44131100513235260.83
02-05-201691.78817.1715147128.959-75000745000029638.8102103613503495.86
01-06-201692.95806.8854146322.0736-75000737500030209.79021106613600636.74349278.905610000024927.89056875072.10942.769765618
01-07-201698.15764.1365145557.9371-75000730000032582.78146109614286511.52
01-08-2016101.38739.7909144818.1462-75000722500033934.20793112714681663.66
01-09-2016104.29719.1485144098.9976-75000715000035080.0652115815028084.46
03-10-2016106.51704.1592143394.8384-75000707500035912.12093119015272984.24
01-11-2016106.98701.0656142693.7728-75000700000036083.84745121915265379.81
01-12-2016104.88715.103141978.6698-75000692500035304.63387124914890722.89
02-01-2017105.12713.4703141265.1995-75000685000035395.26256128114849797.77
01-02-2017110.02681.6942140583.5053-75000677500037159.15288131115466997.25
01-03-2017112.34667.6162139915.8891-75000670000037940.62667133915718150.98
03-04-2017114.63654.279139261.6101-75000662500038680.97357137215963558.37
02-05-2017115.03652.0038138609.6063-75000655000038807.26767140115944263.01
01-06-2017117.89636.1863137973.42-75000647500039685.29986143116265686.48436566.2410000033656.624866343.3763.739624889
03-07-2017118.15634.7863137338.6337-75000640000039763.01312146316226559.57
01-08-2017122.17613.8987136724.7351-75000632500040922.48506149216703660.88
01-09-2017121.32618.1998136106.5352-75000625000040683.72898152316512444.86
03-10-2017121.00619.8347135486.7005-75000617500040592.97521155516393890.77
01-11-2017128.75582.5243134904.1763-75000610000042664.07767158417368912.69
01-12-2017126.73591.8094134312.3669-75000602500042148.66251161417021406.26
01-01-2018130.18576.1254133736.2415-75000595000043019.281164517409783.92
01-02-2018131.61569.8655133166.376-75000587500043366.76544167617526026.75
01-03-2018127.75587.0841132579.2919-75000580000042410.9589170416937004.54
02-04-2018125.36598.277131981.0149-75000572500041789.64582173616545140.03
02-05-2018127.41588.6508131392.3641-75000565000042323.99341176616740701.11
01-06-2018125.78596.2792130796.0849-75000557500041900.54063179616451531.56501586.127710000040158.61277859841.38724.462068086
02-07-2018123.63606.6489130189.436-75000550000041324.92114182716095319.97
01-08-2018128.02585.846129603.5901-75000542500042479.69067185716591851.6
03-09-2018132.54565.8669129037.7231-75000535000043588.72793189017102659.83
01-10-2018128.42584.0212128453.702-75000527500042580.98427191816496024.41
01-11-2018125.99595.2853127858.4166-75000520000041955.71077194916108881.91
03-12-2018126.84591.2961127267.1205-75000512500042177.15232198116142561.57
01-01-2019128.36584.2942126682.8263-75000505000042565.83048201016261007.59
01-02-2019125.32598.4679126084.3584-75000497500041779.04564204115800891.8
01-03-2019126.77591.6226125492.7358-75000490000042159.02816206915908714.12
01-04-2019135.10555.1443124937.5915-75000482500044183.93782210016879068.6
02-05-2019134.02559.618124377.9735-75000475000043935.60663213116669136.01
03-06-2019137.45545.653123832.3205-75000467500044710.80393216317020752.46513441.439810000041344.14398858655.8564.593793775
01-07-2019137.10547.046123285.2746-75000460000044633.47921219116902411.14
01-08-2019129.53579.0164122706.2581-75000452500042858.79719222215894141.61
03-09-2019125.50597.6096122108.6486-75000445000041826.69323225515324635.39
01-10-2019129.63578.5698121530.0788-75000437500042883.59176228315753944.11
01-11-2019137.01547.4053120982.6735-75000430000044613.53186231416575836.09
02-12-2019138.19542.731120439.9425-75000422500044873.00094234516643595.65
01-01-2020140.20534.9501119904.9924-75000415000045304.92154237516810679.93
03-02-2020135.64552.9342119352.0582-75000407500044306.62047240816188913.17
02-03-2020129.46579.3295118772.7286-75000400000042841.4182243615376317.45
01-04-2020103.30726.0407118046.688-75000392500034697.48306246612194222.87
04-05-2020113.93658.299117388.389-75000385000038457.82498249913374059.16
01-06-2020119.56627.3001116761.0889-75000377500040178.57143252713959955.79507475.933910000040747.59339859252.40664.527510376
01-07-2020123.71606.2566116154.8324-75000370000041346.69792255714369514.31
03-08-2020125.51597.5619115557.2704-75000362500041829.33631259014503593.01
01-09-2020133.28562.7251114994.5453-75000355000043763.13025261915326473
01-10-2020126.51592.8385114401.7068-75000347500042091.53427264914472959.93
02-11-2020126.86591.2029113810.5039-75000340000042182.32697268114438000.52
01-12-2020142.68525.6518113284.8521-75000332500045821.06812271016163482.7
01-01-2021153.04490.068112794.7841-75000325000047796.32776274117262113.76
01-02-2021158.65472.7387112322.0454-75000317500048758.27293277217819892.5
01-03-2021173.03433.4508111888.5946-75000310000050939.1435280019360083.52
01-04-2021172.42434.9843111453.6102-75000302500050854.01926283119216831.47
03-05-2021173.44432.4262111021.184-75000295000050996.02168286319255514.16
01-06-2021183.72408.2299110612.9541-75000287500052339.15741289220321811.93558717.036410000045871.70364854128.29645.09685596
01-07-2021186.73401.6494110211.3047-75000280000052704.43957292220579756.92
02-08-2021195.04384.5365109826.7682-75000272500053654.37859295421420612.86
01-09-2021201.95371.3791109455.3891-75000265000054384.7487298422104515.83
01-10-2021213.93350.582109104.8071-75000257500055539.19506301423340791.39
01-11-2021220.96339.428108765.3792-75000250000056158.35445304524032798.18
01-12-2021214.59349.5037108415.8755-75000242500055599.04935307523264962.72
03-01-2022218.47343.2966108072.5789-75000235000055943.60782310823610616.32
01-02-2022226.78330.717107741.8619-75000227500056641.89964313724433699.45
02-03-2022219.03342.4188107399.4431-75000220000055992.32982316623523700.02
01-04-2022228.53328.1845107071.2586-75000212500056782.47932319624468994.73
02-05-2022225.70332.2995106738.9591-75000205000056554.05405322724090983.06
01-06-2022221.24338.9984106399.9607-75000197500056182.20033325723539927.31666136.736710000056613.67367843386.32636.290408186
01-07-2022213.06352.0135106047.9472-75000190000055459.72965328722594575.63
01-08-2022227.98328.9762105718.971-75000182500056738.5297331824101811
01-09-2022231.86323.4711105395.4999-75000175000057044.12145334924437000.61
03-10-2022229.14327.3108105068.1891-75000167500056830.9767338124075324.85
01-11-2022242.59309.1636104759.0255-75000160000057838.32804341025413491.99
01-12-2022246.84303.8405104455.1849-75000152500058133.81138344025783717.85
02-01-2023243.07308.5531104146.6318-75000145000057872.21788347225314921.8
01-02-2023239.40313.2832103833.3486-75000137500057609.64912350224857703.66
01-03-2023240.12312.3438103521.0048-75000130000057661.7941353024857463.67
03-04-2023240.19312.2528103208.752-75000122500057666.84708356324789710.15
02-05-2023246.79303.9021102904.8499-75000115000058130.39426359225395887.91
01-06-2023251.06298.7334102606.1165-75000107500058417.3106362225760291.62689403.7110000058940.371841059.6296.548930111
03-07-2023261.14287.2023102318.9143-75000100000059057.40216365426719561.27
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
I got a picture of the excel sheet. I recreated the whole sheet using real data and formulae. That original sheet has 17.09% on it.
 
Upvote 0
The yellow highlighted columns had wrong formulas (or none at all).

MF SWP Calculator
SchemeWithdrawal PeriodNo of Monthly InstallmentsTotal Withdrawal AmountCurrent Value (As on 3/7/23)Return(%)
ICICI Pru Equity & Debt INF109K01Y071-8-2013 to29-7-202312090000001787432.02917.9%
NAV DateNAVUnitsCumulative UnitsCash FlowNet AmountCapital Gain/LossNo of Days (Invested)Current ValueCapital Gain/Loss in 1 YearCapital Gain ExemptionActual Capital GainGain - Net of all taxesEffective Tax
7/1/1355.51180,148180,14810,000,00010,000,00000$ 10,000,000
8/1/1354.03183,694(3,547)(75,000)9,925,000-271867.48131$ 9,653,133
9/2/1353.14185,359(188,906)(75,000)9,850,000-439301.844263$ 9,410,698
10/1/1355.75175,336(364,242)(75,000)9,775,00042080.7174992$ 9,817,081
11/1/1359.45163,162(527,405)(75,000)9,700,000642859.5458123$ 10,342,860
12/2/1359.93160,604(688,009)(75,000)9,625,000709869.8482154$ 10,334,870
1/1/1462.04153,933(841,942)(75,000)9,550,0001005182.141184$ 10,555,182
2/3/1460.19157,418(999,360)(75,000)9,475,000736717.0626217$ 10,211,717
3/3/1462.42150,593(1,149,953)(75,000)9,400,0001040595.963245$ 10,440,596
4/1/1466.05141,181(1,291,133)(75,000)9,325,0001488046.934274$ 10,813,047
5/2/1467.12137,813(1,428,946)(75,000)9,250,0001600007.449305$ 10,850,007
6/2/1473.78124,356(1,553,303)(75,000)9,175,0002271987.666336$ 11,446,98870967.14166010645.07125889354.92881.182785694
7/1/1478.17116,413(1,669,715)(75,000)9,100,0002637917.36365$ 11,737,917
8/1/1477.95115,779(1,785,495)(75,000)9,025,0002598088.518396$ 11,623,089
9/1/1482.16108,934(1,894,429)(75,000)8,950,0002903085.443427$ 11,853,085
10/1/1483.46106,338(2,000,767)(75,000)8,875,0002972157.321457$ 11,847,157
11/3/1486.8101,382(2,102,149)(75,000)8,800,0003172258.065490$ 11,972,258
12/1/1489.0198,023(2,200,172)(75,000)8,725,0003283760.252518$ 12,008,760
1/1/1590.495,686(2,295,858)(75,000)8,650,0003338478.982549$ 11,988,479
2/2/1592.9992,214(2,388,072)(75,000)8,575,0003456188.838581$ 12,031,189
3/2/1594.5489,909(2,477,981)(75,000)8,500,0003509149.566609$ 12,009,150
4/1/1592.3191,269(2,569,250)(75,000)8,425,0003358682.7639$ 11,783,683
5/4/1590.8491,920(2,661,170)(75,000)8,350,0003247528.622672$ 11,597,529
6/1/1592.289,751(2,750,920)(75,000)8,275,0003292947.397700$ 11,567,947327092.257210000022709.22572877290.77432.523247302
7/1/1591.9489,189(2,840,109)(75,000)8,200,0003249140.744730$ 11,449,141
8/3/1594.7185,788(2,925,897)(75,000)8,125,0003362897.265763$ 11,487,897
9/1/1589.0190,439(3,016,336)(75,000)8,050,0003029715.762792$ 11,079,716
10/1/1590.488,219(3,104,555)(75,000)7,975,0003077961.836822$ 11,052,962
11/2/1592.0885,795(3,190,350)(75,000)7,900,0003137521.72854$ 11,037,522
12/1/1592.484,686(3,275,036)(75,000)7,825,0003124071.97883$ 10,949,072
1/1/1692.3783,902(3,358,938)(75,000)7,750,0003092616.65914$ 10,842,617
2/1/1687.8387,385(3,446,323)(75,000)7,675,0002824274.166945$ 10,499,274
3/1/1684.5889,856(3,536,179)(75,000)7,600,0002612106.881974$ 10,212,107
4/1/1689.4684,116(3,620,294)(75,000)7,525,0002855731.6121005$ 10,380,732
5/2/1691.7881,172(3,701,467)(75,000)7,450,0002944121.8131036$ 10,394,122
6/1/1692.9579,344(3,780,810)(75,000)7,375,0002970629.3711066$ 10,345,629349278.905610000024927.89056875072.10942.769765618
7/1/1698.1574,376(3,855,186)(75,000)7,300,0003171390.7281096$ 10,471,391
8/1/16101.3871,267(3,926,453)(75,000)7,225,0003268995.3641127$ 10,493,995
9/1/16104.2968,559(3,995,012)(75,000)7,150,0003344299.5491158$ 10,494,300
10/3/16106.5166,426(4,061,437)(75,000)7,075,0003387710.0741190$ 10,462,710
11/1/16106.9865,433(4,126,870)(75,000)7,000,0003367825.7621219$ 10,367,826
12/1/16104.8866,028(4,192,898)(75,000)6,925,0003259794.5271249$ 10,184,795
1/2/17105.1265,164(4,258,062)(75,000)6,850,0003232767.3141281$ 10,082,767
2/1/17110.0261,580(4,319,641)(75,000)6,775,0003356710.1441311$ 10,131,710
3/1/17112.3459,640(4,379,282)(75,000)6,700,0003389362.6491339$ 10,089,363
4/3/17114.6357,795(4,437,076)(75,000)6,625,0003416819.3321372$ 10,041,819
5/2/17115.0356,942(4,494,018)(75,000)6,550,0003389168.0431401$ 9,939,168
6/1/17117.8954,924(4,548,942)(75,000)6,475,0003426164.2211431$ 9,901,164436566.2410000033656.624866343.3763.739624889
7/3/17118.1554,168(4,603,111)(75,000)6,400,0003393110.4531463$ 9,793,110
8/1/17122.1751,772(4,654,883)(75,000)6,325,0003451129.5741492$ 9,776,130
9/1/17121.3251,517(4,706,399)(75,000)6,250,0003390310.7481523$ 9,640,311
10/3/1712151,033(4,757,432)(75,000)6,175,0003342154.9591555$ 9,517,155
11/1/17128.7547,379(4,804,811)(75,000)6,100,0003470011.651584$ 9,570,012
12/1/17126.7347,542(4,852,353)(75,000)6,025,0003385942.5551614$ 9,410,943
1/1/18130.1845,706(4,898,059)(75,000)5,950,0003412862.9591645$ 9,362,863
2/1/18131.6144,639(4,942,699)(75,000)5,875,0003397063.2931676$ 9,272,063
3/1/18127.7545,401(4,988,100)(75,000)5,800,0003279780.8221704$ 9,079,781
4/2/18125.3645,668(5,033,768)(75,000)5,725,0003189942.9641736$ 8,914,943
5/2/18127.4144,345(5,078,113)(75,000)5,650,0003188407.5031766$ 8,838,408
6/1/18125.7844,323(5,122,437)(75,000)5,575,0003114606.8531796$ 8,689,607501586.127710000040158.61277859841.38724.462068086
7/2/18123.6344,488(5,166,924)(75,000)5,500,0003030494.2171827$ 8,530,494
8/1/18128.0242,376(5,209,300)(75,000)5,425,0003072697.6251857$ 8,497,698
9/3/18132.5440,365(5,249,666)(75,000)5,350,0003109329.2591890$ 8,459,329
10/1/18128.4241,076(5,290,742)(75,000)5,275,0002994862.561918$ 8,269,863
11/1/18125.9941,273(5,332,015)(75,000)5,200,0002908929.281949$ 8,108,929
12/3/18126.8440,405(5,372,420)(75,000)5,125,0002882105.4081981$ 8,007,105
1/1/19128.3639,342(5,411,763)(75,000)5,050,0002866099.2522010$ 7,916,099
2/1/19125.3239,698(5,451,461)(75,000)4,975,0002771343.3612041$ 7,746,343
3/1/19126.7738,653(5,490,114)(75,000)4,900,0002754389.842069$ 7,654,390
4/1/19135.135,714(5,525,828)(75,000)4,825,00028425002100$ 7,667,500
5/2/19134.0235,442(5,561,270)(75,000)4,750,0002782588.422131$ 7,532,588
6/3/19137.4534,012(5,595,283)(75,000)4,675,0002786973.4452163$ 7,461,973513441.439810000041344.14398858655.8564.593793776
7/1/19137.133,552(5,628,835)(75,000)4,600,0002737520.0582191$ 7,337,520
8/1/19129.5334,934(5,663,769)(75,000)4,525,0002585814.0972222$ 7,110,814
9/3/19125.535,458(5,699,227)(75,000)4,450,0002481717.1312255$ 6,931,717
10/1/19129.6333,750(5,732,977)(75,000)4,375,0002501542.8532283$ 6,876,543
11/1/19137.0131,385(5,764,362)(75,000)4,300,0002557842.4932314$ 6,857,842
12/2/19138.1930,574(5,794,935)(75,000)4,225,0002527845.722345$ 6,752,846
1/1/20140.229,601(5,824,536)(75,000)4,150,0002506872.3252375$ 6,656,872
2/3/20135.6430,043(5,854,579)(75,000)4,075,0002407326.3792408$ 6,482,326
3/2/20129.4630,898(5,885,476)(75,000)4,000,0002284875.6372436$ 6,284,876
4/1/20103.337,996(5,923,472)(75,000)3,925,0001815834.9472466$ 5,740,835
5/4/20113.9333,793(5,957,265)(75,000)3,850,0001974168.3492499$ 5,824,168
6/1/20119.5631,574(5,988,839)(75,000)3,775,0002022321.4292527$ 5,797,321507475.933910000040747.59339859252.40664.527510377
7/1/20123.7129,909(6,018,748)(75,000)3,700,0002039770.4312557$ 5,739,770
8/3/20125.5128,882(6,047,630)(75,000)3,625,0002021751.2552590$ 5,646,751
9/1/20133.2826,636(6,074,266)(75,000)3,550,0002071454.8322619$ 5,621,455
10/1/20126.5127,468(6,101,734)(75,000)3,475,0001950241.0882649$ 5,425,241
11/2/20126.8626,801(6,128,535)(75,000)3,400,0001912265.492681$ 5,312,265
12/1/20142.6823,304(6,151,839)(75,000)3,325,0002031400.6872710$ 5,356,401
1/1/21153.0421,236(6,173,075)(75,000)3,250,0002071174.2032741$ 5,321,174
2/1/21158.6520,013(6,193,088)(75,000)3,175,0002064100.2212772$ 5,239,100
3/1/21173.0317,916(6,211,004)(75,000)3,100,0002105484.5982800$ 5,205,485
4/1/21172.4217,544(6,228,548)(75,000)3,025,0002051112.112831$ 5,076,112
5/3/21173.4417,009(6,245,557)(75,000)2,950,0002005843.5192863$ 4,955,844
6/1/21183.7215,649(6,261,206)(75,000)2,875,0002006334.3682892$ 4,881,334558717.036410000045871.70364854128.29645.09685596
7/1/21186.7314,995(6,276,201)(75,000)2,800,0001967632.412922$ 4,767,632
8/2/21195.0413,971(6,290,172)(75,000)2,725,0001949442.4222954$ 4,674,442
9/1/21201.9513,122(6,303,294)(75,000)2,650,0001921594.4542984$ 4,571,594
10/1/21213.9312,037(6,315,331)(75,000)2,575,0001906845.6973014$ 4,481,846
11/1/21220.9611,314(6,326,645)(75,000)2,500,0001871945.1483045$ 4,371,945
12/1/21214.5911,301(6,337,946)(75,000)2,425,0001797702.5963075$ 4,222,703
1/3/22218.4710,757(6,348,702)(75,000)2,350,0001752899.7123108$ 4,102,900
2/1/22226.7810,032(6,358,734)(75,000)2,275,0001718137.6223137$ 3,993,138
3/2/22219.0310,044(6,368,778)(75,000)2,200,0001642441.6753166$ 3,842,442
4/1/22228.539,299(6,378,077)(75,000)2,125,0001608836.9143196$ 3,733,837
5/2/22225.79,083(6,387,160)(75,000)2,050,0001545810.8113227$ 3,595,811
6/1/22221.248,927(6,396,087)(75,000)1,975,0001479464.6093257$ 3,454,465666136.736710000056613.67367843386.32636.290408186
7/1/22213.068,918(6,405,004)(75,000)1,900,0001404979.8183287$ 3,304,980
8/1/22227.988,005(6,413,010)(75,000)1,825,0001380637.5563318$ 3,205,638
9/1/22231.867,548(6,420,557)(75,000)1,750,0001331029.5013349$ 3,081,030
10/3/22229.147,310(6,427,867)(75,000)1,675,0001269225.1463381$ 2,944,225
11/1/22242.596,595(6,434,463)(75,000)1,600,0001233884.3323410$ 2,833,884
12/1/22246.846,178(6,440,641)(75,000)1,525,0001182054.1653440$ 2,707,054
1/2/23243.075,965(6,446,606)(75,000)1,450,0001118862.8793472$ 2,568,863
2/1/23239.45,744(6,452,350)(75,000)1,375,0001056176.9013502$ 2,431,177
3/1/23240.125,414(6,457,764)(75,000)1,300,000999471.09783530$ 2,299,471
4/3/23240.195,100(6,462,864)(75,000)1,225,000941891.83563563$ 2,166,892
5/2/23246.794,660(6,467,524)(75,000)1,150,000891332.7123592$ 2,041,333
6/1/23251.064,282(6,471,805)(75,000)1,075,000837314.78533622$ 1,912,315689403.7110000058940.371841059.6296.548930111
7/3/23261.143,829(6,475,635)(75,000)1,000,000787432.02883654$ 1,787,432
 
Upvote 0
BigBeachBananas said:
The yellow highlighted columns had wrong formulas (or none at all).
Click to expand...
Firstly, thank you for taking an interest and attempting to answer my question.

I just checked your sheet and found that you had some cell formulae different to mine. This is how it should be:
Units Column = Abs (Cash Flow Column/ NAV Column)
Current Value Column = NAV Column * Cumulative Units Column This formula does not apply to the first row of the table.

I wonder how some of the columns do not have the formulae - maybe I was not skilled enough in using ExcelBB (I guess I selected it as Table Only and not as a Mini Sheet because the Mini Sheet can not accept the data I have). I will produce my sheet again and hope it will all display fine this time around.

I am facing the same issue with copying my sheet as a Mini Sheet due to its size. Is it okay if I share the same excel sheet as a link? It is here and is titled "SWP - CaseStudy-SheetOnly.xlsx".

Also, I share the link to the pictures of the spreadsheet that I tried to emulate on Excel: Here.

Is there any other way to securely share my Excel sheet here?
 
Upvote 0
polarwoc said:
Firstly, thank you for taking an interest and attempting to answer my question.

I just checked your sheet and found that you had some cell formulae different to mine. This is how it should be:
Units Column = Abs (Cash Flow Column/ NAV Column)
Current Value Column = NAV Column * Cumulative Units Column This formula does not apply to the first row of the table.

I wonder how some of the columns do not have the formulae - maybe I was not skilled enough in using ExcelBB (I guess I selected it as Table Only and not as a Mini Sheet because the Mini Sheet can not accept the data I have). I will produce my sheet again and hope it will all display fine this time around.

I am facing the same issue with copying my sheet as a Mini Sheet due to its size. Is it okay if I share the same excel sheet as a link? It is here and is titled "SWP - CaseStudy-SheetOnly.xlsx".

Also, I share the link to the pictures of the spreadsheet that I tried to emulate on Excel: Here.

Is there any other way to securely share my Excel sheet here?
Click to expand...
As a reasonable check, you're withdrawing 75k each month and gaining less 75K each month, your Current Value cannot exceed 10M (the initial investment).
 
Upvote 0
BigBeachBananas said:
As a reasonable check, you're withdrawing 75k each month and gaining less 75K each month, your Current Value cannot exceed 10M (the initial investment).
Click to expand...
Correct. There is a regular 75K withdrawal each month, which is reducing the Cumulative Units and Net Amount columns. But, as months go by, the NAV (Net Asset Value) is improving, and that is in turn improving the Current Value (this is the amount you will get if you withdraw in that month). So, despite withdrawing 9M, you still have 26.7M to withdraw at the end of 10 years. The Capital Gain/Loss column is only computing the capital gain/loss on the units (worth 75K) sold.

Question now is to get to the formula that computes Returns as 17.09%.
 
Upvote 0
I have found a solution where the required 17.09% returns result was obtained.

For this, I went to a new sheet, listed all the dates, in an adjacent column where corresponding first date has -1000000 (10 Million), subsequent dates have 75000 against them. Then, I duplicated the last date, that being 03-07-2023 and against it, I entered the amount of Current Value as on 03-07-2023, which is -26719561.27.

Then, I used a simple =XIRR() function to arrive at the desired 17.09%.
 
Upvote 0
I have found a solution where the required 17.09% returns result was obtained.

For this, I went to a new sheet, listed all the dates, in an adjacent column where corresponding first date has -1000000 (10 Million payment), subsequent dates have 75000 (receipt) against them. Then, I duplicated the last date, that being 03-07-2023 and against it, I entered the amount of Current Value as on 03-07-2023, which is 26719561.27 (receipt).

Then, I used a simple =XIRR() function to arrive at the desired 17.09%.
SWP - CaseStudy-SheetOnly.xlsx
ABCD
101-07-2013-1000000017.09%
201-08-201375000
302-09-201375000
401-10-201375000
501-11-201375000
602-12-201375000
701-01-201475000
803-02-201475000
903-03-201475000
1001-04-201475000
1102-05-201475000
1202-06-201475000
1301-07-201475000
1401-08-201475000
1501-09-201475000
1601-10-201475000
1703-11-201475000
1801-12-201475000
Sheet1
Cell Formulas
RangeFormula
D1D1=XIRR(B1:B122,A1:A122)


SWP - CaseStudy-SheetOnly.xlsx
AB
11401-12-202275000
11502-01-202375000
11601-02-202375000
11701-03-202375000
11803-04-202375000
11902-05-202375000
12001-06-202375000
12103-07-202375000
12203-07-202326719561.27
Sheet1


Just showing the top few rows and bottom few rows because XL2BB would not allow me to display the large amount of data in my sheet.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
128
vancher19
V
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top