Share your knowledge at the LQ Wiki.
 Home Forums HCL Reviews Tutorials Articles Register Search Today's Posts Mark Forums Read
 LinuxQuestions.org A formula for 'applicable figure' in IRS form 8962
 General This forum is for non-technical general discussion which can include both Linux and non-Linux topics. Have fun!

Notices

 12-18-2018, 11:17 AM #1 RandomTroll Senior Member   Registered: Mar 2010 Distribution: Slackware Posts: 1,090 Rep: A formula for 'applicable figure' in IRS form 8962 I do my taxes with a spreadsheet. I want a formula to calculate 'applicable figure' (stupid name!) in IRS form 8962. Has anyone done this? I found a page in Google Groups that isn't good enough. I decided it was hopeless, created a lookup table. I made a CSV version, good for 2018's taxes, as of 2018 December 18. You can find it at https://pastebin.com/4UZN0tJg . I use sc; if anyone wants the sc version, ask. Last edited by RandomTroll; 12-18-2018 at 01:35 PM. Reason: Answered my own question.
 12-18-2018, 01:38 PM #2 michaelk Moderator   Registered: Aug 2002 Posts: 17,934 Rep: Been a few years since I have had to do logical formulas. Untested but here is an Excel example. Code: `=IF(L5>=300,0.0956,IF(L5>132,L5*0.0006-0.0496,0.0201))` Similar for libreoffice except it uses ; instead of , I think. Code: `=IF(L5>=300;0.0956,IF(L5>132;L5*0.0006-0.0496;0.0201))` L5 is your 8962 line five cell value. Crap... After I paid more attention to the table I noticed that it isn't a simple first order equation... You might be able to graph the values and see what a higher order equation looks like and replace it with mine. Last edited by michaelk; 12-18-2018 at 01:48 PM. Reason: oops...
 12-18-2018, 02:00 PM #3 MensaWater LQ Guru   Registered: May 2005 Location: Atlanta Georgia USA Distribution: Redhat (RHEL), CentOS, Fedora, CoreOS, Debian, FreeBSD, HP-UX, Solaris, SCO Posts: 7,561 Blog Entries: 15 Rep: So I went off and decided there is no obvious math for the values in the table at and IRS 8962 Instructions. Accordingly I created the list of values with vim and sort: (Start with 0 since any number below 133 = 0.0201, end at 300 since any number at or above [to 400] = 0.0956. [The PDF doesn't say whether it is possible to go above 400 and if so what to do.]) 0 0.0201 133 0.0302 134 0.0308 135 0.0314 136 0.0320 137 0.0326 138 0.0332 139 0.0338 140 0.0344 141 0.0350 142 0.0355 143 0.0361 144 0.0367 145 0.0373 146 0.0379 147 0.0385 148 0.0391 149 0.0397 150 0.0403 151 0.0408 152 0.0412 153 0.0417 154 0.0421 155 0.0426 156 0.0431 157 0.0435 158 0.0440 159 0.0445 160 0.0449 161 0.0454 162 0.0458 163 0.0463 164 0.0468 165 0.0472 166 0.0477 167 0.0482 168 0.0486 169 0.0491 170 0.0495 171 0.0500 172 0.0505 173 0.0509 174 0.0514 175 0.0519 176 0.0523 177 0.0528 178 0.0532 179 0.0537 180 0.0542 181 0.0546 182 0.0551 183 0.0555 184 0.0560 185 0.0565 186 0.0569 187 0.0574 188 0.0579 189 0.0583 190 0.0588 191 0.0592 192 0.0597 193 0.0602 194 0.0606 195 0.0611 196 0.0616 197 0.0620 198 0.0625 199 0.0629 200 0.0634 201 0.0638 202 0.0641 203 0.0645 204 0.0648 205 0.0652 206 0.0655 207 0.0659 208 0.0662 209 0.0666 210 0.0669 211 0.0673 212 0.0676 213 0.0680 214 0.0683 215 0.0687 216 0.0690 217 0.0694 218 0.0697 219 0.0701 220 0.0704 221 0.0708 222 0.0711 223 0.0715 224 0.0718 225 0.0722 226 0.0726 227 0.0729 228 0.0733 229 0.0736 230 0.0740 231 0.0743 232 0.0747 233 0.0750 234 0.0754 235 0.0757 236 0.0761 237 0.0764 238 0.0768 239 0.0771 240 0.0775 241 0.0778 242 0.0782 243 0.0785 244 0.0789 245 0.0792 246 0.0796 247 0.0799 248 0.0803 249 0.0806 250 0.0810 251 0.0813 252 0.0816 253 0.0819 254 0.0822 255 0.0825 256 0.0828 257 0.0830 258 0.0833 259 0.0836 260 0.0839 261 0.0842 262 0.0845 263 0.0848 264 0.0851 265 0.0854 266 0.0857 267 0.0860 268 0.0863 269 0.0865 270 0.0868 271 0.0871 272 0.0874 273 0.0877 274 0.0880 275 0.0883 276 0.0886 277 0.0889 278 0.0892 279 0.0895 280 0.0898 281 0.0901 282 0.0903 283 0.0906 284 0.0909 285 0.0912 286 0.0915 287 0.0918 288 0.0921 289 0.0924 290 0.0927 291 0.0930 292 0.0933 293 0.0936 294 0.0938 295 0.0941 296 0.0944 297 0.0947 298 0.0950 299 0.0953 300 0.0956 I imported that into an Excel workbook splitting it into 2 columns (using text import with space as delimiter) in one sheet I called "Lookup". In that the actual values above are in columns B and C and rows 5 through 173. In another sheet in same work book I input a field for line 5 of 8962 (in cell C5 of that other sheet) then in the column next to it placed the following formula: =VLOOKUP(C5,Lookup!\$B\$5:Lookup!\$C\$173,2,TRUE) For any value below 133 it will give the Applicable Figure for "0" as it is 0 to 132. For any value above 300 it will give the Applicable Figure for 300. One could modify the formula to give an error if the value is above 400 given that the instruction doesn't define that - I didn't. This site has a tutorial for VLOOKUP (with a slight error - it shows the lookup range as one column but that gives a #REF if you do that - you have to tell it both columns in your lookup table). By the time I got back to post this I see OP already figured out to do a lookup table and the first responder figured out it wasn't a simple formula. I figured I'd share just as another example. Last edited by MensaWater; 12-18-2018 at 02:05 PM.
12-18-2018, 04:04 PM   #4
RandomTroll
Senior Member

Registered: Mar 2010
Distribution: Slackware
Posts: 1,090

Original Poster
Rep:
Quote:
 Originally Posted by michaelk Crap... After I paid more attention to the table I noticed that it isn't a simple first order equation... You might be able to graph the values and see what a higher order equation looks like and replace it with mine.
It's worse than that: the increment isn't monotonic: from 160 to 161 it goes up by .0005, from 161 to 162 it goes up by .0004, from 162 to 163 it goes up by .0005. It's hopeless.

Quote:
 Originally Posted by MensaWater [The PDF doesn't say whether it is possible to go above 400 and if so what to do.])
There is no applicable figure above 400 because there's no PTC if you make that much.

The table you have is what I put in my pastebin 'cause I didn't want to clutter up LQ.

Last edited by RandomTroll; 12-18-2018 at 09:23 PM. Reason: Fixed mistake

12-18-2018, 04:43 PM   #5
MensaWater
LQ Guru

Registered: May 2005
Location: Atlanta Georgia USA
Distribution: Redhat (RHEL), CentOS, Fedora, CoreOS, Debian, FreeBSD, HP-UX, Solaris, SCO
Posts: 7,561
Blog Entries: 15

Rep:
Quote:
 Originally Posted by RandomTroll The table you have is what I put in my pastebin 'cause I didn't want to clutter up LQ.
I hate relying on external links for key data because the external link locations may have changed or gone defunct by the time someone sees the post. There is many a historical post I've gone to with bad links that made it useless because the important details weren't in it. Worse yet other places screen scrape to make their own posts and often don't even have the links when you find them.

One of my blog posts relates to a post I found about an error after time change and it was maddening because I appeared to find multiple hits related to the error in a web search but every hit link I opened turned out just to be a copy of the original post which didn't answer the question.

I wasn't trying to steal your thunder. I noted in my post that you'd resolved it and said what you did before I posted - I just felt like, having gone through the trouble, I should share the details including the formula and some discussion.

12-19-2018, 01:31 AM   #6
RandomTroll
Senior Member

Registered: Mar 2010
Distribution: Slackware
Posts: 1,090

Original Poster
Rep:
Quote:
 Originally Posted by MensaWater I hate relying on external links for key data...
Yeah, those pastebin links have a limited life. Back when I had my own website I would have posted it there and alerted the search engines. I always thought I was the only person writing his/her own spreadsheet to do taxes (+my friend Rick), but if there are others, it's useful to have it in a permanent place.

To document what I did:

1) pdftexted i8962.pdf
2) edited out everything but the table's entries
3) grepped on 0.0 for applicable figures
4) grep -ved on 0.0 for income ratios
5) imported those into those into a spreadsheet; because that's sc for me, a short script that incremented the row and set that cell's value to each entry in the file, and allowed me to combine the 2 and have sc put them parallel.
6) then I merged it into Taxes2018.sc, used an @index to point to it.

Quote:
 Originally Posted by MensaWater I wasn't trying to steal your thunder. I noted in my post that you'd resolved it and said what you did before I posted - I just felt like, having gone through the trouble, I should share the details including the formula and some discussion.
I wasn't slanging you; it's the right thing to do.

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Manish87 Linux - Security 2 07-06-2010 07:59 PM TheIndependentAquarius Programming 11 06-14-2010 09:39 AM LNarine Linux - Software 2 06-10-2010 09:48 PM ic_torres Linux - Newbie 1 08-08-2008 06:32 AM King V Linux - Distributions 7 04-02-2002 06:16 AM

LinuxQuestions.org

All times are GMT -5. The time now is 04:52 AM.

 Contact Us - Advertising Info - Rules - LQ Merchandise - Donations - Contributing Member - LQ Sitemap -