XIRR is a classic and widely used Excel function which is very useful for calculating rates of return on investments given an initial and final value and a series of cash inflows and outflow in irregular amounts at irregular intervals (e.g. Jan. 15, invested $200, Mar 12, withdrew $100, ...). Although iWork '09 Numbers doesn't have this function, it can be simulated, as described below.
XIRR takes as input a series of dates and cash flow amounts, and computes the annualized rate of return over the time period with the correct allowance for the time impact of the transactions (earlier transactions have had more time to make an impact). As a typical application, you might want to calculate your yearly rate of return given an irregular set of investments and withdrawals, and that's the example we'll consider here. The Numbers IRR function calculates a per-interval return rate given an irregular series of inflows/outflows at fixed intervals. Here's how to use it to simulate XIRR:
Create a Cash Flow Summary Table with columns Date, Amount (and optionally Notes). For every cash event flow in a year, enter the date it occurred on (Numbers is smart and will recognize and normalize any dates you enter), and the net amount for that day: positive for inflows (investments) and negative for outflows (withdrawals).
Create another Cash Flow Daily table with only two columns: Date and Amount. In the first row of this column, enter the date as 'Start:' and for the amount, the starting account value, as a positive number. In the second row, enter the date 1/1/year for the year of interest. Select it and pull down from the circle at lower right to create a date entry for each and every day, through 12/31/year.
Starting on the date 1/1 (e.g. in cell B2), enter in the Amount column the formula:
Where Cash Flow Summary is the name of the table listing the cash flows. Select this cell and drag it down to copy this over the rest of the days in the year. This will select any cash flow amounts from the Cash Flow Summary table above on the given date.
On the last day, 12/31, edit the formula, subtracting the final balance of the account. So, if there were no investments or withdrawals to the account on that day, this cell will contain the negative final account balance. You might consider referencing the initial and final account values from another, more convenient location.
Anywhere else, you can now compute the annualized rate of return as:
=(IRR(Cash Flow Daily::Amount,(1+Guess)^(1/Days in Year)-1)+1)^Days in Year-1
Where Guess is an (annualized) initial guess for the return rate (like .05, or 5%), and Days in Year is the number of days in the year (365 or 366 for leap years).
(Optional, for appearance) Select all the rows of the Cash Flow Daily table except the first and last, click on the black triangle which appears at left for any of them, and Hide Selected Rows.
Now you can add any number of days with inflow/outflow to the summary table and the return value is updated at once. As a check, for most cash flow streams, the computed annualized return will lie between the two values:
Pretending the total yearly net inflow occurred at the beginning of the year, increasing the initial account value:
Final Value/(Initial Value+Net Inflow)-1
Pretending the total net inflow occured at the end of the year, reducing the final account value:
(Final Value-Net Inflow)/Initial Value-1
[crarko adds: I haven't tested this one. I am curious to know how many of you have successfully replaced Excel with Numbers. I'd guess that would be the most difficult piece of Office to do without.]
I was nearly done laying out a photo book in iPhoto when I got a coupon for half off a photo book printed from Snapfish. I wanted to take advantage of the coupon but didn't want to have to redo all the work I had put in laying out the book. With a modest amount of work I was able to transfer my book over. Here's how:
First I exported the photo book from iPhoto to a PDF by right clicking in the background of the photo book layout and choosing 'Save book as PDF". Then I created an Automator workflow to create a JPG image from each page of the PDF that went like this (just search for each command, drag it over, and select the option as indicated below):
Get selected Finder items
Render PDF Pages as images
Move Finder Items (to Desktop)
Rename Finder Items (Make Sequential)
Save the workflow. After running the PDF through this workflow I had a series of sequentially numbered images files, one for each page of the book. I uploaded these to Snappish and used the full page layout option to make each image a full page. Presumably you could do something similar for other Photobook printing sites.
I did have to reproduce the layout of the front and back covers in Snappish, and everything else was just as I had it in iPhoto. Of course the layout in Snappish was static and could not be tweaked, so make sure you have everything just the way you want it in iPhoto before transferring it over.
[crarko adds: I haven't completely tested this one, but the Automator workflow was pretty simple to create.]
When the rainbow pinwheel spins over a specific application's window, but changes to a pointer or cursor or finger etc. over every other applications' windows, I figure that app has frozen. I'll usually give it a minute or two to see if it'll thaw on its own. If not, traditionally, I would get my Force Quit window open before I start clicking around on any other app's windows for fear that the freeze might spread. But recently I tried something that has also worked for me a few times since, even with entire system freezes.
However, for reasons that will become obvious in the hint, I can only recommend you try this with a MacBook, MacBook Pro, or MacBook Air. Though you may have success with desktops by inducing a similar state; I have not tried.
Simply close the computer. Wait for it to go to sleep, as indicated by the pulsing light on the front of the case. I will usually wait a bit longer (around 5min).. I find that the more desperate i am for it to work, the longer i'm willing to wait. When I open it back up the rainbow pinwheel is gone. I'll often find an alert window wanting me to confirm an error or stop some unresponsive script or something, but without having having to Force Quit anything. I've got control back, and/or I didn't lose anything except some minutes -- sometimes.
I don't expect this trick to work for every freeze, and neither should you. But it's worked for me enough times that it's worth a try every time.
[crarko adds: I remember doing this sort of thing all the way back to the PowerBook 5300, and it's always been about 50/50 with success. One thing that does happen when putting the machine to sleep is that AirPort also goes inactive, leading to the method of this hint. I might try that first before putting the machine to sleep.]
Looking to turn your holiday music off, but don't want to spend an hour clicking and scrolling through your iTunes library?
If you have an extensive collection of holiday songs in your iTunes library like I do (750 and counting) and it's time to turn the cheer off, here's an easy way to do it.
Assuming you have the songs all listed as 'Holiday,' do a search in iTunes for 'holiday.'
That will bring up only those songs. Select all of the songs found, then hold the Command button and click in the check box of any song. This will deselect ALL of them. Clear the search and you have your total library back, with Bing, Nat and the MacKenzie Brothers taking a well-earned rest. No more 'Grandma got run over by a Reindeer' till next Christmas!
[crarko adds: Simple but effective. Judicious use of playlists can accomplish the same feat.]
I've been using Word for the Mac since v1.0, so I have a lot of older documents created in early versions. At some point I discovered that Word (I forget which version) dropped support for the earliest document formats -- you could retrieve the text, but that was it. I complained to Microsoft and was basically told 'tough patooties.'
So it was a very pleasant surprise when I went to open an early document, resigned to the prospect of extracting the text (the file was so old it had even lost its type and creator codes and looked like a Unix file to the Finder; I had to choose 'Open any file' to select it) and Word opened it with all formatting, styles etc. completely intact!
Okay, so maybe this doesn't qualify as a hint, but it is sure nice to know.
[crarko adds: I don't have any ancient Word documents left to try this out with, but a hint with 'tough patooties' in it seems like the perfect way to start the new year.]
Turns out there is a super easy way to export contact info from Address Book to a spreadsheet, if you have Numbers (from iWork).
Use Address Book to select the individual contacts, or select a complete group.
Drag the contacts/group to an open sheet in Numbers, a new table will be created with all your contacts.
By default only a few details are shown (last+first name, phone, e-mail address). All the other info is available as well, but hidden. Feel free to hide/unhide the columns you are interested in.
If you were one of those (as I was) who followed the hint regarding session-only cookies in Safari, then you are possibly still being prompted for your password in iTunes, at least in Lion.
I discovered, after trying every hint I could find without success, that if the user permissions for ~/Library/Cookies/ is still set to read only for the user, then you will still be prompted for your password the first time you activate iTunes after a login.
The file, ~/Library/Cookies/Cookies.binarycookies is not being created/updated.
[crarko adds: I haven't tested this one. It seems clear if a user doesn't have write access to a directory in their own Library it could cause problems like this. If somebody who has used the previous hint mentioned and is still on 10.5 or 10.6 can comment on whether they are affected by this, it would be appreciated.]
When an attachment's icon is highlighted in the message section of Mail, choosing Forward creates a message with the attachment as its only content.
When I needed to send someone just the file I got by mail, I used to remove the text body of an email message by hand and add some nice text. Recently, after having opened a file I went back to Mail's main window where the attachment was still selected. I hit Command+Shift+F and the new message contained only the Subject line, a short 'start of new message' text and the attachment.
[crarko adds: It's been generally true in Mail that selected text in a message is all that would be included in a reply/forwarding of that message. This special case of that rule is perhaps worth noting, given its usefulness and the fact that not everyone may be aware of it.]
Just discovered this by accident. While in Preview.app when you when you slide three fingers down on your track pad you can get a preview of the top most recent documents opened in a Cover Flow-like menu along the bottom go the screen.
[crarko adds: This is another of those things I wasn't able to reproduce with the Magic Trackpad, so I don't now if this is the result of some setting or perhaps a third-party utility I don't have. Try and see if you can make this work, and please post your results in the comments if you do.]
