Solver Equivalent in Numbers

Question

Level 1

8 points

Solver Equivalent in Numbers

Hi, I have been using Office Excel for years and would like to change over to Numbers. My issue is that I have a spreadsheet that calculates the Annualized Return of my investments using the Solver feature in Excel. I have opened the spreadsheets in Numbers and have looked at the formula options but I can not figure out how to make it solve the Annualized Return. Does anyone have any suggestions or am I stuck paying for Microsoft Office in order to make use of the Solver option?

Thank you,

Jill

iMac 21.5″ 4K, macOS 10.14

Posted on Jan 16, 2022 2:47 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,054 points

Jan 17, 2022 9:35 AM in response to oneyellowlab

XIRR requires at least one positive and one negative value. All the buys should be positive, all the sells negative, and the final amount should be negative (as if you sold it on that date).

Looking at your screenshot, if F7 is the $13,592 then F23 is the -$23,065. It looks like there is something in the row above the $-23,065. Maybe that is the problem.

Reply

Answer 2

oneyellowlab Author

Level 1

8 points

Jan 16, 2022 8:25 PM in response to oneyellowlab

This is what the spreadsheet looks like

And this is what the solver is doing - it is taking the N-Balance to zero by changing the N-Return.

It is this action that I'm trying to replicate.

Thanks,

Jill

Reply

Answer 3

oneyellowlab Author

Level 1

8 points

Jan 17, 2022 9:29 PM in response to oneyellowlab

Hi,

You were so helpful with my original problem that I'm hoping you can help me take this formula one step further. I now have it working on my three separate investments - they are in one file with 3 separate tabs. What I would like to do is get a XIRR value for all three investments together. Under my old Excel I could do this but it involved having another tab that gathered all the data together which is time consuming and sometimes finicky if the data didn't link over and update correctly. What I was hoping to do is get Numbers to do the work and give me an XIRR value for all three investments through the formula pulling from the different tabs. I came up with this but I'm getting an error "XIRR requires between 2 and 3 arguments, but was given 1"

I also tried this but I get The range CDZ i Shares::Table 1::F7:F22 can’t be used as a single value.

Do you know if I can get this XIRR formula to calculate off of 3 different tabs or do I have to stick to my old method of creating a tab that has all the information in one sheet?

Thank you!

Jill

Reply

Answer 4

Barry

Level 9

59,951 points

Jan 16, 2022 5:39 PM in response to oneyellowlab

Solver often relies on reiterated calculations in which the formula references a cell whose content depends on the value in the cell containing the formula. Numbers does not permit this type of calculation, and returns a self-reference error message.

There is a solver-type feature in the open source (and free) LibreOffice application which might do yhe task you need. The link is to the home page, where you'll find a download link.

Reply

Answer 5

Badunit

Level 7

28,054 points

Jan 16, 2022 6:33 PM in response to oneyellowlab

Is the annualized return something that requires solver? I thought Solver was more for optimization problems. Annualized return should be a straightforward calculation. What calculations are you doing in Excel with Solver?

Annualized Return =( (Current_Value/Initial_Value)^(365/Days_Owned) ) - 1

Compare that to the answers you are getting in Excel

Reply

Answer 6

oneyellowlab Author

Level 1

8 points

Jan 16, 2022 8:12 PM in response to Badunit

The spreadsheet was made by someone else so I'm not really clear on everything it is doing. You enter every transaction of the investment with the date, the price, any dividends, purchases, withdrawals and at the bottom of the sheet you put in the price and date that you are working with. There is a field that once you hit the solver - it brings that field to 0 and you get a rate of return in a different field. I have years of investment details in these spreadsheets and I'd like to be able to keep using them but I really was hoping to be able to ditch Excel as other than this I really don't need Excel.

Reply

Answer 7

Badunit

Level 7

28,054 points

Jan 17, 2022 5:14 AM in response to oneyellowlab

Hoefully someone who uses the financial functions chime in here. I think the XIRR function will do what you want to get "N-Return".

=XIRR(cashcolumn,datecolumn)

This has to include the value and date you are working with (Dec 31 and -$23,065).

If N-Return is in C28 then it would be

=XIRR(F8:F24,A8:A24)

Try that in a cell somewhere and you should get the same percentage as in C28.

Reply

Answer 8

Badunit

Level 7

28,054 points

Jan 17, 2022 5:45 AM in response to Badunit

Additional: My guess is this spreadsheet was designed before the XIRR function was introduced. I think it was introduced in Excel 2007 for PC and Excel 2011 for Mac but I am not sure. XIRR does an iterative calculation, sounds like what Solver is doing for you.

https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d

Reply

Answer 9

oneyellowlab Author

Level 1

8 points

Jan 17, 2022 9:08 AM in response to Badunit

I appreciate your help but it is not working. I tried the formula, and it is giving me the error - Argument 1of XIRR is invalid. The first deposit occurs in cell F7 so I adjusted the formula accordingly for both colums but I can't figure out why it is calling it invalid. And you are right this spreadsheet would date back to probably the late 90s, very early 2000s.

It looks like that IRR formula requires a negative deposit, I tried adjusting that first deposit to be negative but the error is the same.

Reply

Answer 10

oneyellowlab Author

Level 1

8 points

Jan 17, 2022 9:42 AM in response to Badunit

You are a genius! I had only tried deleting the formatting in that row which didn't help, but once I deleted the entire row the formula worked! That's awesome, thank you so much I really appreciate all of your help!!

Reply

Answer 11

oneyellowlab Author

Level 1

8 points

Jan 18, 2022 9:47 AM in response to Badunit

Ok thanks. And yes you are correct about the date order, on my one "combo" sheet that I'm making in Numbers it took me awhile to figure out why the XIRR formula wasn't working and then once I moved the investment with the earliest start date to the top it started working. The solver in Excel doesn't require this in order to work.

Thanks again for all of your help!

Reply

Answer 12

Badunit

Level 7

28,054 points

Jan 18, 2022 6:09 PM in response to oneyellowlab

Yes, that is what I meant by it had to be in date order, but it didn't sound that way when I read it back so I deleted that bit from my post. It apparently also doesn't like empty rows (rows that do not have date and amount) included in the ranges.

It appears that one way to ensure the first date is the earliest is to use a fake date that is way before any of your actual entries. The dollar amount for that date will be $0. It has no effect on the result.

Reply

Answer 13

oneyellowlab Author

Level 1

8 points

Jan 16, 2022 8:06 PM in response to Barry

Thanks, I wasn't sure how secure that was to be using but I'll take a further look at it.

Reply

Answer 14

Badunit

Level 7

28,054 points

Jan 18, 2022 9:46 AM in response to oneyellowlab

I think you'll have to do it the way you are doing it. There may be a simple way to do it but I'm not seeing it.

Reply