網頁

2015年4月27日 星期一

NEC退稅稅務資料抽取試算表 (2013~2023)
快速取得六家主要ETF發行公司的稅務資料

當NEC退稅試算表實用化確立之後,輸入基金公司的稅務資料變成了NEC退稅中相對麻煩的部分,也是造成錯誤的主要來源之一。對於熟悉試算表操作的人來說,利用試算表的公式和剪貼、填滿功能也可以省不少事,但對初學者來說,這似乎還挺難理解的,特別是一些經由PDF檔案轉換過來的試算表,由於表格有時會錯位,更容易出錯。這裡針對有在關注的6家知名ETF公司的稅務文件,做成系列自動化稅務資料抽取試算表放在雲端硬碟分享:
本系列試算表使用了很多Excel沒有的功能,只能配合Google雲端硬碟使用使用時請先建立副本,就能輸入自己的資料,無須試圖取得編輯權限。如無Google帳號,需先建立帳號,才能在自己的雲端硬碟建立副本。

使用方式很簡單,只要選擇對應基金公司和年份的試算表,在淺綠色格子填入需要的ETF代碼,就會列出對應稅務資料,只需選取、複製,然後到NEC退稅試算表,按右鍵>選擇性貼上>僅貼上值,就OK了。



手動輸入ETF代碼如果查無資料,會用紅色粗體提示,請確認是否輸入錯誤。此外,2015年有些債券ETF整年都沒有配息,會用刪除線提示。

其實要更懶的話,連代碼都不需手動輸入,只要從戶頭的Position列表把代碼複製下來,也不用區分,直接把整個列表貼進去各個試算表,試算表會自動跳過不屬於該公司的部分 (沒有的部分用紅字標示)。Vanguard、iShare、Powershare、SPDR (2015~)、WisdomeTree (2019~) 沒有限制代號數目,其他則有50個的限制,不過應該也夠用了 (要增加也很方便,有需要請留言)。

如果發現試算表無法正確解析的基金資料,後文的相關連結中有2012年以來退稅相關稅務資料的系列文章,可找到原始文件,或是直接下載我保存的備份,自行取得所需稅務資料。

2022年SPDR系列ETF沒能拿到QII數據

由於蒐集文件時基金公司已將相關文件下架,詢問客服回應是不會再提供。有相關美國債券ETF的話要自己想辦法,向基金公司或券商取得數據。如果實在沒辦法取得,而券商有提供正確的QII退稅和1042-S再分類的話,是也可以從每筆退稅金額佔原始預扣稅的比例來推估可能的QII比例或者其平均值,這雖然不是正確的數字,但可以用來確認券商提供的1042-S和實際帳目的預扣稅是一致的。如果實在沒有正確數字,要不要就用這個逆推的數字申報,請自行斟酌。

Invesco DB系列原物料期貨ETF要到自己到官網找

因為稅務結構不同,此類ETF在美國人是K-1表格為主要報稅依據,和一般ETF的1099-DIV表格大不相同,所以一般ETF的稅務資料並未涵蓋此類ETF,因此必須自己到Invesco官網個別ETF資料網頁的配息紀錄找。

其他請自行到官網找或利用Google搜尋

例如PIMCO的歷年配息資料到官網各ETF介紹頁找,QII資料則在Resources頁面。搜尋的話可以利用ETF代碼、基金公司名稱,加上Tax、QII (Qualified Interest Income)、ROC (return of capital) 、Foreign Tax Credit 等關鍵字搜尋。

其他不能直接套用的狀況

以下說明一些大部分人不會碰到,但碰到了會需要另外手動處理的狀況。使用上沒問題的話都可以忽略不看:
  1. 有些ETF年末配息會出現同一派息日有兩筆資料的情形,一筆是股息,另一筆是長短期資本利得。試算表會以紅色背景色和文字信息提醒關注。本來必須手動將兩筆資料加總整併成一筆,再填入NEC退稅試算表,但因為試算表預設為擷取最大值,所以如果同一欄位的兩筆資料只有一個數值,試算表就可以自動處理,但仍需看實際狀況確認。例如2022年Vanguard的BIV、BND,以及某些PowerShare和Market Vectors ETF。
  2. Exempt-interest dividends: 主要發生在美國市政債券 (Municipal bond) ETF。根據2019年網友們提供的報稅資料,SchwabTD Ameritrade和Firstrade的1042-S都將之歸類於免稅,也沒有預扣稅,但三家公司所得分類方式不同,NEC退稅試算表的處理方式也不相同。SchwabFirstrade的最新退稅試算表已經有針對性的調整,擷取試算表也做了調整,已經可以直接使用。TD Ameritrade版本沒有提供此功能,他是把此類收入與QII放在一起,但沒有預扣稅,所以如果有碰到,可以把Exempt-interest dividends加入Income Dividend、QII per share兩個欄位,然後手動把預扣稅設為0,計算結果就可以相符,當然,報表的Note能自行增加相關說明會更好。不過,市政債券ETF對於NRA還是有潛在的不利之處,更別提是有些高息的市政債券,其實風險不低,別因為免預扣稅和高配息就貿然投入。
  3. Cash liquidation distribution:ETF清算後配發的本金,照說是不該被扣稅,但沒實際碰到過,也不清楚帳目如何記錄,對NEC退稅試算表的影響不明。有碰到的朋友歡迎分享。目標到期債券基金 (defined-maturity bond funds) 到期後返還本金也屬於此類,有網友提供嘉信的實例研究,其到期債券ETF的交易紀錄Action (行動)欄位標示為Final Cash Liquid,和最後一筆配息同時入帳但分別記錄,沒有預扣稅,1042-S的分類是Income Code 37,但實際分類計算有些問題,這裡有詳細的討論。
相關文章

美國國外投資人要報稅嗎? 能退稅嗎?
配息超過(取消)個人免稅額後的報稅考量
美國券商NRA投資人NEC退稅總整理 (含退稅試算表/報表)
2021年NEC退稅相關稅務資料來源整理
2020年NEC退稅相關稅務資料來源整理
2019年NEC退稅相關稅務資料來源整理
2012年NEC退稅相關稅務資料來源初探
再論國際ETF配息是否為國外來源?
Income Code 27與DBC或其他原物料期貨基金的稅務問題 MLP ETF的股息可能含有大量ROC美國市政債基金 (Municipal bond funds) 的稅務結構與潛在問題

[附註] Google試算表的國別設定與日期格式轉換經驗歸納:

  1. 如果日期欄位是以日期資料儲存的,無論試算表語言代碼設定為何,均可自由變更日期格式。
  2. 如果日期欄位是以文字資料儲存的,如果符合語言代碼對應的格式,就能藉由設定日期格式將其轉換為日期資料。若設為台灣 (台灣地區預設),必須嚴格遵守【年/月/日】格式才行。若設為美國,則YYYY/M/D和M/D/YYYY都可以接受,但YY/M/D則會被誤判為M/D/YY,產生錯誤日期。
  3. Datevalue也依循上述原則運作,但變更語言代碼並不會改變試算表原有的計算結果,必須變更公式或引數的儲存格內容,才會按新的語言代碼規則重新計算。
  4. 藉由PDF轉換工具產生的試算表檔案,日期均以文字格式儲存,所以必須將試算表語言代碼設為美國,日期的運算和操作才能正常運作。

2023更新內容:

  1. iShare的原始檔案轉換EXCEL格式再次失敗,得比2022更離譜,還好終於又找到一個還能轉的工具,缺點是只能轉成許多分頁需手動整併,而且轉換時誤增的空白欄位比過去更加不規律,無法再用算式判斷,於是只好在整併時順便對齊全部欄位,讓抽取資料的算式簡單化,不過這樣結果應該會比較可靠。這工具轉換後,百分率數字為文字格式,其他數字均為數字格式,兩者需要分開處理。更新為v2 。EFV的ORDINARY DIVIDENDS問題仍與2022相類,原因不明。
  2. SPDR 今年總算來得及下載QII資料,。
  3. VanEck新增一個章節,且ROC章節欄位變動,更新為v1.23。連帶發現過去幾年版本多有錯漏,2017已經逾期不管,2018從缺,2019起都更新為v1.22。不過好像這家ETF沒甚麼人關注都沒有報錯。

2022更新內容:

  1. iShare的原始檔案轉換EXCEL格式失敗,改用其他工具後配息數字也變成文字格式,需要手動轉換資料才能套用公式,表頭資料也需要調整。此外,EFV用ORDINARY DIVIDENDS=DIVIDEND INCOME+SS/T CG+Foreign Tax Paid的計算結果和Total distribution欄位數字不相符,而後者與其他管道的提供的配息數字,只好改用Total distribution扣除其他類別配息來計算,故更新為v1.16,但這個變更使得後面的驗算欄位失去意義。不過檢視了其他幾個國際基金,並沒有此類情形,不知道是否為單一錯誤或另有緣故。
  2. SPDR 缺QII資料。
  3. VanEck新增一個章節,且ROC章節欄位變動,更新為v1.23。連帶發現過去幾年版本多有錯漏,2017已經逾期不管,2018從缺,2019起都更新為v1.22。不過好像這家ETF沒甚麼人關注都沒有報錯。

2021更新內容:

  1. Vanguard Primay Layout的TOTALS列標示改變,更新為v1.13。
  2. iShare的日期改為mm/dd/yyyy,故更新為v1.15。DIVIDEND INCOME、SS/T CG從2020年開始沒有直接提供,但今年才做了完整的應對,同時也把2020年更新為v1.14r,所幸對熱門ETF沒有影響。
  3. VanEck的ROC章節欄位變動,更新為v1.22。同時Exempt-interest dividends欄位函式新增配息僅有ROC配息的狀況。

2020更新內容:

  1. 去年新增Muni支援後,不少人誤把稅務資料貼入Exempt-interest dividends,導致計算錯誤,今年將稅務資料的欄位順序做了調整,以盡量避免誤貼問題。
  2. WisdomTree 2020年稅務資料也改為標準的ICI格式,直接移植套用省事不少。
  3. iShare的欄位大變樣,正確性也還不確定,使用時要小心注意排除錯誤。
  4. VanEck Vectors 2020年其實大致延續2019年的表格編排,但新增一個章節還是導致試算表需要調整,由於原始結構就複雜,花了不少時間才研究解決,不過考慮原理沒有大改變,版本編號只小改為v1.21。

2019更新內容 (如無特別說明,均只針對2019年版的變動,2013-2018無改變)

  1. iShare的錯位型態又有改變,須調整對應函式 (感謝 V.H. 指正)。
  2. SPDR的QII又延遲更新,再次調整部分交易代碼須更新QII查詢方式為v2.13
  3. Invesco 工作表名稱有小變動,抽取試算表也相應調整為v1.09。
  4. VanEck Vectors 2019年的表格編排又有變動,抽取試算表也相應調整為v1.2。原始資料內容雖比過去少,但表格簡化使錯位問題比過去更少,檢索函式也大為簡化。
  5. WisdomTree 2019年主要檔案直接以 Excel 格式提供,試算表也相應調整為v2.0,檢索函式大為簡化。

2018更新內容 (如無特別說明,均只針對2018年版的變動,2013-2017無改變)

  1. 微調各試算表市政債ETF的提示格式和文字。
  2. iShare的新增欄位造成FSI%右移,須調整對應函式。
  3. SPDR:QII轉換表格仍有標註去2016年改名的SPDR portfolio系列的舊代碼,對轉檔效果影響更大,需要用新的條件算式解讀。檔案格式雖為試算表,但日期需要手動將試算表地區設定為美國,設定好日期格式,才能正確讀取。
  4. Invesco (原Powershares)系列ETF的品牌改了,名字和CUSIPs也跟著變了,但交易代號不變。檔案格式雖為試算表,但日期需要手動將試算表地區設定為美國,設定好日期格式,才能正確讀取。
  5. VanEck Vectors 2018年的檔案有許多資料是掃描圖,無法有效轉換為Excel格式,沒有辦法製作抽取試算表,連搜尋結果不完整,所以只能自己按目錄和排序一一檢視,然後自行輸入。修改2016~2017年的檔名,因為2016年5月系列ETF的品牌改了,名字和CUSIPs也跟著變了,因為交易代號不變不影響試算表功能,所以先前沒注意更新檔名。
  6. WisdomTree的錯位方式又有改變,各欄位定位方式需要調整,同時發現部分函式冗贅、邏輯不周延,因為調整後才凸顯出來。過去版本未造成明顯錯誤,故未往前更新。
  7. Schwab在2018年終於找到官網連結,只可惜沒有統整的稅務資料檔案,無法製成稅務資料抽取試算表,QII資料也仍待驗證。詳情請參考2018年NEC退稅相關稅務資料來源整理的說明。

2017更新內容 (如無特別說明,均只針對2017年版的變動,2013-2016無改變)

  1. 微調各試算表市政債ETF的提示格式和文字。
  2. SPDR:QII轉換表格稍有位移,且由於有標註去年改名的SPDR portfolio系列的舊代碼,影響轉檔效果,需要設定條件算式解讀。另外,17年12月底的配息日期不知何故被標到1月初,需要自己手動改一下才能搭配Tax Summary。
  3. Market Vectors 2017年的檔案格式大改,Excel格式錯位的問題大為改善,但將稅務資料拆成幾個不同Section的編排整個解讀方式需要重建。QII資料未再提供,目前無解。
  4. WisdomTree的錯位方式和往年不同,位移變化性變大,各欄位定位方式需要調整。

2016更新內容 (如無特別說明,均只針對2016年版的變動,2013-2015無改變)

  1. Vanguard的Secondary layout格式改變,調整對應公式
  2. iShare的Distribution Summary Information新增Cash Liquidation Distribution及Exempt Interest Dividends欄位,需調整國外來源%的公式,並增加更多驗證信息
  3. SPDR的Primary layout有些空欄位被填空白,篩選引用、附註、條件化格式之公式需調整;日期欄位以文字方式儲存,需先設定試篹表國別為美國,再重新設定為日期格式才能正常讀取為日期;QII轉換表格稍有位移,且QII %去年沒%今年有,故對應公式需微調
  4. SPDR在2016年有兩隻市政債券(Municipal bond) ETF曾經變更CUSIP,而不同CUSIP的國外來源%是分開表列的,當試算表利用ETF代碼(Ticker)查詢國外來源%時會不知道要取哪一筆而出現【#REF!】的錯誤信息。碰到的話將國外來源% (0) 手動填入就行了。
  5. PowerShare和Market Vectors有些ETF的年末配息會出現同一派息日有兩筆資料的情形,一筆是股息,另一筆是長短期資本利得。這問題過去已經有處理,但2016在QII也同步出現此現象,故需調整QII查詢公式,以避免查詢結果超過一列造成錯誤。
  6. Market Vectors的PDF轉檔造成QII標題變形、國外來源標題改變,須調整查詢條件。

2015更新內容 (如無特別說明,均只針對2015年版的變動,2013-2014無改變)

  1. 更新2015年NEC退稅稅務資料以及2013年WisdomTree的QII資料 (抽取公式需調整)
  2. SPDR改用Excel格式發布稅務文件,其試算表需大改版,資料正確性已經沒有問題 (其實是改套Vanguard公式,反而更輕鬆)
  3. 沒有配息的ETF變多了,現在輸入ETF代碼如果查無資料,會用紅色粗體提示,請確認是否輸入錯誤。整年都沒有配息,會用刪除線提示,以方便找出代碼錯誤。
  4. 修正Market Vectors 2013、2014的小錯誤 (不影響資料正確性)
  5. Market Vectors 增加2015 QII內容,國外來源抽取公式也有修正,部分配息記錄無加總行也需調整公式
  6. WisdomTree原始資料增加不少欄位,公式需調整
  7. PowerShare的FSI%以前沒有%,現在有%

SPDR 2013-2014的PDF檔轉換問題比較多

試算表有特別加強驗證計算,把有問題的部分用不同顏色標出來較可能出問題的是L/T CG、ROC和國外收入百分比,前二者透過總配息 (Total Distribution) 的比對應可察覺大多數錯誤,後者則沒有任何驗證依據,只能請大家自己用原始PDF資料或其備份對照確認。2014年甚至原始PDF檔本身就有部分資料多出一列,完全不知如何對齊,只能自己透過其他資訊來源校正。

91 則留言:

  1. 太感謝Rib大了,週末來用用看再來回報。

    但上週末正打算開始準備報稅資料時,第一個遇到的問題的確就是交易記錄與稅務資料的日期格式,本來想手動改,但由於對試算表不是很熟,也不知道自己手動改會不會有問題,,

    日期格式真的頗討厭,之前想一併算自己在台灣券商的投資跟在美國券商的投資(i.e., 總資產)的annual return,也是卡在日期格式不一致,最後只好手動一筆一筆改日期格式,但發現這樣很麻煩也好,是個頻繁交易的"反誘因".. :)

    回覆刪除
    回覆
    1. 這部分我發現最方便的就是利用Google試算表,因為不同試算表可以設定不同國別,就可以針對不同日期格式正確解讀。先分別把美國資料的試算表設為美國,台灣資料設為台灣。要整合時,把美國資料的日期顯示格式改為YYYY-MM-DD,然後就可以正確貼進台灣試算表。這也是我弄這些試算表時的做法。

      刪除
    2. 問一下 "不同試算表可以設定不同國別"的實際作法

      因為我只看到對"整個雲端硬碟"設定語言,其實我本來怕會不會這裡一改成英文,原本台灣資料檔案的日期格式又亂掉了,看起來好像不會,似乎是根據試算表"建立時"用的語言來決定日期格式。 這裡我沒把握,還請大家分享一下心得..

      刪除
    3. 檔案>試算表設定

      雲端硬碟國別設定會影響建立試算表時預設的國別,但建立之後文件的國別設定不會受雲端硬碟設定影響,不然就失去設定的意義了

      這也是Google試算表比Excel強的地方。

      刪除
  2. 這個地方有點不懂,手上的ETF有一堆美國及非美來源的.....
    但 1042S 裡面只列了 Income code: 01, 06, 29, 36, 37
    06 理論上就是美國來源的,但是如果不參考買賣交易歷史明細,要怎麼把etf對應的incomde dividend 填入呢?

    Thanks!

    回覆刪除
    回覆
    1. 1.目前券商的預扣稅原則是--只要美國注冊基金,一律都是美國來源。我是根據基金公司稅務文件,重新再分類,主張其部分屬於國外來源,可以免稅。如果就按券商分類,那麼通常完全無法退稅。雖然可以順利退稅,但其實也可能有些爭議,詳情參考[http://ribtw.blogspot.tw/2014/07/international-ETF-dividend-source.html]

      2.我也不太懂您的問題,這個歷史交易明細有何關聯? 配息時只考慮紀錄日帳上是否有持股,有持股即按美股配息金額給予派息。而該派息的稅務特性則紀錄於基金公司的稅務文件中,用各類收入與每股配息金額的比例,就可以算出該筆配息各類收入之詳細數字。不過,限於資料來源,國外收入的部分只會是粗估(詳前連結),不像QII、ROC每一筆配息都有精確的數字,這也是我的退稅方式另一個有爭議的地方。

      3.請參考各試算表的說明,案指示填入資料,試算表會自動抓取每一筆配息對應的稅務資料,計算再分類後的結果。至於細節,還請看NEC退稅總整理一文。

      刪除
  3. Rib大,我正試著報2016的退稅,在2015稅務資科抽取試算表,把2016 Vanguard的Pri./Sec./NRA資料貼上,要抽取2016的資料,但是會出現ERROR,請教一下怎麼解決?

    回覆刪除
    回覆
    1. 2016還沒研究呢,這陣子忙

      刪除
    2. Vanguard除了國外來源%資料格式有變,公式需要調整外,其他倒是和過去一樣。目前文章雖未更新,但Vanguard 2016檔案已經上架。

      如果還是想知道自己動手的問題出在哪裡,可以分享檔案連結給我研究看看。

      刪除
    3. Rib大,我不太會用google doc的試算表,所以我手動比對過(不會寫公式XD),Vanguard的資料跟Rib大的一樣,謝謝

      刪除
  4. Rib大,要請教您,如何建立新的稅務抽取表?我有用Schwab戶頭買他們家的ETF,要報稅要查稅務資料,不知如何下手?可否請您分享做法,感謝!

    回覆刪除
    回覆
    1. 您要先取得Schwab ETF的稅務資料,才談得上能不能抽取。但不幸的,目前為止的網路分享,似乎沒有成功取得的經驗。所以除非您打算用ECI報稅。目前Schwab ETF連手動完成Reclass Report都有困難。我也不太清楚這要去跟哪裡申訴。qii是只有基金公司應該要提供的,所以他們有這個義務。其他除了國外來源%以外,晨星倒是有資料。而國外來源沒資料的處理可以參考本部落格的相關討論。

      當然,放棄NEC改用ECI報稅也是一個選擇。

      刪除
    2. 收到!今年我試著用手動建立看看行不行!也把Schwan的ETF賣了,免得明年又遇到同樣問題!謝謝了!

      刪除
  5. 感謝 Rib 大,今年第一次報稅,採用 NEC 方式,退稅已在七月中匯入 TD 帳戶,一毛不差。要是沒有 Rib 大製作的稅務資料抽取試算表,完全無法想要如何著手。

    我在想應該要把退稅金額的一部份回饋給 Rib 大, 5% 、 10% 之類的。請問 Rib 大有沒有提供什麼贊助管道?

    回覆刪除
    回覆
    1. 我那個是自用兼興趣自學搞出來的,能對大家有用當然更有成就感。您的留言支持就是很好的回饋了,至於退稅的分享,願意的話,就捐給您覺得需要的公益單位吧。有列舉扣除的話還可以退點稅。如果沒有甚麼特別的想法,我個人推家扶<http://www.ccf.org.tw/>。

      刪除
    2. 好的,方才已將退稅金額的 7% 線上捐款給家扶。再次感謝 Rib 大無私分享。

      刪除
  6. 感謝rib大, 研究了幾天慢慢地摸清楚如何用NEC報稅, 要是沒有您的教學真的無從開始起. 想請問rib大, 我是用TD報稅的, 但因為裡面還有購買AAPL股票, 報表會欠缺這方面資料. 想請問我自己要去尋找稅務資料填進excel檔, 應該要怎麼做?(只能找到divend@@)感謝~><

    回覆刪除
    回覆
    1. 個股也只有dividend了。其他類別收入是基金/ETF才有的。

      刪除
  7. Rib 大大, 請教一下. 我的情況跟小羊類似. 都是TD 購買 大部分 Vanguard 的 ETF, 但是有一些美國公司的股票會發股利. 我試著在退稅試算表v4.09中的 "1.輸入稅務資料" 增加兩行這間公司“Payable Date”還有"Income Dividend", 然後後面的欄位都抄VTI 的0, (因為假設都是美國境內資產收入). 在 "2. 輸入交易紀錄" 也複製貼上了我的交易紀錄. 但是在右邊的 Reclassification Summary 這裏, 有關這間公司的黃色格子部分, 都出現 "#N/A" (no match are found in filter evaluation). 即使是Payable date 或是 income dividend 也都依樣. 可以麻煩您解惑一下是哪裡出了問題嗎?

    回覆刪除
    回覆
    1. 可以的話請分享試算表連接,比較能抓出問題

      刪除
  8. Rib你好

    我有投資BLV與BIV
    這次報稅時,會顯示稅務資料欠缺2018−01−02的資料
    應該如何處理呢

    謝謝

    回覆刪除
  9. DBC 很久沒配息了,
    Ex Date Distribution NAV Income S/T Cap Gain L/T Cap Gain Return Cap Total
    Dec 24,2018 14.23 0.1885 0.0000 0.0000 0.0000 0.1885
    Dec 15,2008 0.00 0.3400 0.0000 0.0000 0.0000 0.3400

    嘉信在下面時間有記錄
    12-31-2018 NRA Tax Adj DBC INVESCO DB COMMODITY INDEX TRACKING FUND
    12-31-2018 Reinvest Dividend DBC INVESCO DB COMMODITY INDEX TRACKING FUND

    但在 Invesco (原Powershare) 2018 稅務資料抽取試算表 v1.08
    這個抽取表會找不到,請問要如何處理? 感謝@@

    回覆刪除
    回覆
    1. 爬原始檔案就沒有,當然抽不到。您既然有查到資料,就自己填入吧 (pay date 12/31/2018)

      https://www.invesco.com/portal/site/us/investors/etfs/product-detail?productId=dbc

      刪除
  10. Rib大你好:最近在整理2018年的報稅資料時發現,DBC有一筆PARTNERSHIP DISTRIBUTION,在1042-S上被歸類為Income code 27,稅率為37%。透過試算表運作不會被抓出來,想請問是否有什麼辦法調整,謝謝!

    回覆刪除
    回覆
    1. 2018年針對Partnership的ECI有新增Partnership withholding,NRA稅率為37%,收入類別為27。不過DBC的年報指出,DBC並沒有ECI,所以你的券商分類和預扣稅都是錯的。不過這個鐵定需要手動調整試算表才行,不知道你的券商是甚麼? 用的是哪個版本的試算表? 可以分享連結嗎?

      刪除
    2. 2018年針對Partnership的ECI有新增Partnership withholding,NRA稅率為37%,收入類別為27。不過DBC的年報指出,DBC並沒有ECI,所以你的券商分類和預扣稅都是錯的。不過這個鐵定需要手動調整試算表才行,不知道你的券商是甚麼? 用的是哪個版本的試算表? 可以分享連結嗎?

      https://ribtw.blogspot.com/2019/05/Invesco-DBC-and-Partnership-withholding.html

      刪除
    3. 收到連結了,大概知道TD的做法,你的解讀沒錯。由於目前版本沒有做INCOME CODE 27的功能,改公版要花不少時間,而且DBC很少配息,所以未必會動手改公版,不過還使可以自行手動調整:

      首先提醒一下,DBC的稅務資料中的Income Dividends應為0。

      1.選取退稅計算頁的Q3:U欄,複製,貼上數值,然後再末端列手動加上DBC的配息和預扣稅,Q欄填1。

      2.BC7加上INCOME CODE 27收入。BF5填YES。

      3.把報表複製到編輯頁,Note [4]30% Tax改成Withholding Tax,DBC對應的數字改成實際預扣稅。

      4.Note [5] - Income Code 06 = Dividend  - QII - ROC + Total NP 改成 - Income Code 06+27 = Dividend + PTP distribution (DBC) - QII - ROC + Total NP

      不會改的話請開放編輯給我

      刪除
    4. 已經改完。
      Note [2] 再加註 DBC K-1的說明。

      此外,STPZ的稅務資料不對,QII都是100%。
      PIMCO的稅務資料可以在這裡找到:
      http://www.pimcoetfs.com/Resources/Pages/default.aspx

      刪除
    5. 謝謝!真的很佩服您如此用心!我想跟您確認一下"列印報表"分頁中的內容:
      Note [4] 列舉的DBC tax是依30%計算的2.43,這邊我是否照"編輯報表"分頁中
      改成3.00,連帶總合改成7.37。另外,Note [5]也照"編輯報表"分頁做修改。
      再次謝謝您的熱心與耐心!

      刪除
    6. 是的,請用編輯報表的內容。自動產生的報表要改算式,牽涉較廣,短時間很難完成,有沒有必要也是問題。

      刪除
    7. Rib大你好:首先再次感謝你依然為大家提供很重要的協助!今年整理2019年報稅資料嘗試自己鍵入並編輯DBC相關資料,想請問一個在編輯報表時遇到的狀況:我如果修改報表內容,除了我鍵入的資料格,其餘報表的部分會變成空白,請問你是否也遇過類似狀況呢?如果有的話想請教如何排除

      另外這個年度Firstrade中有列出income code 01的收入,但退稅試算表並沒有抓到,想問有沒有其他人也遇到類似的狀況。

      刪除
    8. 1.請到試算表的詳細說明文,裡面有編輯報表的步驟。

      2.請確認稅務資料裡的QII資料是否完整,其次FT的試算表要v4以後的版本。

      如果問題還是無法解決,請留下試算表連結。

      刪除
    9. 報表編輯方法問題看來你已掌握,DBC數字修改應該沒錯,不過income code和再分類核對的地方要手動補上income code 27的說明,才能和1042-相符。QII的落差則是先前網友先發現的,FT很可能是漏了IEI在12月兩筆配息的QII。

      刪除
    10. 表格補充和核對公式都沒問題,只有FT的部分有兩個建議:

      1.既然核對方式調整過了,[4]核對結果的說明應該是完全相符。

      2.[5]的表格很好,建議再增加報表QII總合和1042-S Income Code 01稅前收入的比較,應該會更完整

      刪除
    11. 謝謝提醒!我修改如下[連結還有作用]:
      [4] Checking reclassification results against gross income on Form 1042-S, all items matched.
      [5] 後面補上了"Income code 01 on 1042-S" 和 "Total QII after reclassification" 的數據,然後加註: The discrepancy between total QII after reclassification and income code 01 on 1042-S matched to the sum of QII listed above, after rounding off cents to whole dollars.

      刪除
    12. 有個建議,是否可以把你針對IEI問題的註解(含表格)去個人化後提供給大家分享? DBC的說明也是,不過DBC的問題各家券商狀況可能不同,可能要另外統整。

      刪除
    13. 再看了你的說明又想到,因為你有特別說明K-1的interest income和dividend income的免稅,那麼是否要說明這部分收入沒有列在這份summary裡?

      我之前是完全忽略這部分的,不過我覺得你這樣寫更完整。據說美國人搞K-1其實挺麻煩,DBC畢竟是有價證券而不是一般的parternership,應該有特別替投資人考慮稅務簡化。以NRA來說本來可以預扣了事,但既然主動申報,能交代清楚些是更好。

      刪除
    14. 突然想到一件事,先前有網友說,他WIP和BWX沒QII資料的時候完全相符,反之,WIP和BWX的QII想必也不在FT的Code 01裡。再回去看你的試算表,果然,這個也考慮進去的話CODE 01連小數點的誤差都沒有了。

      刪除
    15. 沒問題,希望能和大家找到一個方便套用的格式讓一切更容易處理。

      至於DBC收入部分,其實我本身對K-1的認識還是半知半解,上面列出來的數據只有cash distribution會真的出現在帳戶紀錄上,這個我目前不確定怎麼處理,要再研究一下。

      剛好BWX的兩筆QII也都在12月同樣的時間,我想我會再一起整理上去,謝謝分享!

      刪除
    16. 已經參考了你的做法,調整成比較廣泛適用的形式公開分享,特此致謝
      https://docs.google.com/spreadsheets/d/1aQQ-DQ60FrAk5xVsBSTcmrcJtJbnIwArVg97LjeqoQI

      刪除
    17. DBC部分也加入更新的文章中了,謝謝。

      註解內容我有稍作調整,或許也可以參考一下。
      至於文章中算式寫法雖有不同,但都是相通的。

      刪除
    18. 再次的調整後又更加完整,感謝!

      刪除
  11. 請問INVESCO DB 的 DBC 的稅務務抽取資料
    在您附的Invesco稅務抽取試算表找不到DBC 這要去哪裡找呢
    我上了Invesco的網站 要試圖讀取K-1但是失敗

    回覆刪除
    回覆
    1. https://www.invesco.com/portal/site/us/investors/etfs/product-detail?productId=dbc

      刪除
  12. 第一次學習利用NEC報稅,在稅務資料抽取試算表的使用遇到問題,請教是否有逐步教學的影音說明?在列出ETF代碼後,Income Dividend等資料并無更新.

    回覆刪除
    回覆
    1. 請留下試算表的連結,我可以幫你看一下

      刪除
  13. 不好意思 , 如果是個股而非ETF , ex : AAPL / GOOG
    要如何抽取稅務資料或哪邊可以查詢手動填入 ??
    謝謝 ~

    回覆刪除
    回覆
    1. 請自行Google吧,XXXX dividend history就可以找到

      刪除
  14. Rib大您好,小弟是 firstrade 的使用者,今年第1次嘗試用 NEC 的方式報稅,我在使用您提供很厲害的 NEC退稅試算表時,有一些個人使用的心得,向您及有用 firstrade 的使用者交流。

    我有買 Vanguard 的 BND,在用 2018 年的NEC退稅稅務資料抽取試算表時,我在 firstrade 的交易資料有一筆是2018-09-06的配息,而在"Vanguard 2018 稅務資料抽取試算表 v1.11F"中,時間是寫2028-09-07,所以,在計算上會有小障礙,若有人遇到相同問題,可以試一下把"Vanguard 2018 稅務資料抽取試算表 v1.11F"的 BND 此筆的日期改為 2018-09-06即可。

    回覆刪除
    回覆
    1. 感謝分享。這還真怪,第一次聽說這種情形。

      刪除
    2. 而在"Vanguard 2018 稅務資料抽取試算表 v1.11F"中,時間是寫2028-09-07。應該是"2018-09-07",而非"2028-09-07",打錯了....

      刪除
  15. Rib大您好,看了你的文章,實在佩服!Orz
    我是報稅新手,今年第一次嘗試申辦退稅,只覺得要研究的東西好多喔,只能對你佩服的五體投地!另外,想請問您會整理 2019 年的NEC退稅稅務資料抽取試算表嗎?
    感謝您了

    回覆刪除
    回覆
    1. 施工中
      目前Vanguard, iShare, Invesco已完成, SPDR缺QII,已可在目錄中找到

      刪除
    2. RIB大您好...

      你的檔案真是及時雨
      我弄了半天...資訊還欠東欠西..你的資料就做完了...
      不過VT的Foreign source 真的是0呀?

      刪除
    3. 實際上當然不會是零,但可能是VT為美國投資人選擇的稅務處理方式會使該稅務資料中的國外來源所得為0,以至於我們可能找不到比較正規的數據來計算VT實際上的國外來源收入。關於這個問題的細節討論,或是這樣要怎麼退稅的問題,請參考http://ribtw.blogspot.com/2014/07/international-ETF-dividend-source.html

      刪除
  16. Rib大, iShare的抽取試算表裡面的EFV的資料看起來怪怪的, 可以抽空看一下嗎?

    回覆刪除
  17. Rib 大你好,今年我使用稅務資料抽取試算表退稅,但似乎2019年SPDR的ITE,IPE稅務資料沒有提供,導致TD試算表有所誤差,是否可請Rib大幫我看一下,謝謝!

    回覆刪除
    回覆
    1. 這兩隻改名了,ITE改SPTI,IPE改SPIP,只能自行把稅務資料中對應月份的新代碼改回就舊代碼

      刪除
    2. Rib大你好,如你所說確實只要把稅務資料裡的ITE改SPTI,IPE改SPIP,即可解決這問題,但我更改後TD試算表再分類後的income code 01+04與income code 06金額跟1042-s仍有差距, 可否再麻煩你看一下,謝謝!

      刪除
    3. 抱歉,我忽略了稅金退還紀錄的問題,請反過來,把CSV裡的ITE, IPE透過搜尋、取代全部改為新的交易代碼,稅務資料請還原。保險起見,請先做副本,然後選定交易代碼欄位做取代。

      刪除
    4. 對了,由於稅金退還金額大致相符,退還稅金配對那裏建議就不要手動配對了,比較省事也簡化報表。

      刪除
  18. 請問一下,因為我有設定利息自動再投入所以CSV資料裡同一天會有ORDINARY DIVIDEND (IPE),W-8 WITHHOLDING (IPE),Bought IPE三筆資料,似乎只要把ORDINARY DIVIDEND SYMBOL的部份分別改為SPTI 及 SPIP,試算表即可再分類,不知我這樣做是否即可,謝謝!

    回覆刪除
    回覆
    1. 看過你改的結果了,很OK。雖然稅金退還的部分也是W-8 WITHHOLDING,但因為是今年的紀錄所以都已經是新代碼了。

      其實可以選取整個SYMBOL欄位,Ctrl-H,搜尋特定範圍,全部取代。這樣操作4次就能完工 (稅務、CSV各兩次),不必一筆筆去找。

      刪除
    2. 原來如此,這也是一個辦法.我是根據試算表跟我告知有哪些日期缺乏稅務資料,反過來去找CSV上這些日期的ORDINARY DIVIDEND SYMBOL做更改,其實還蠻快的.這樣看來我是可以來準備寄今年的退稅資料了,謝謝!

      刪除
  19. Rib大大,謝謝您多年多無私的貢獻。
    想請教您,近日我用NEC退稅試算表TD Ameritrade 2016~2018 V4.09 計算,發現 Taxable Income沒有因為試算表而減少,反而是一樣的數字,以往都可以退一半左右,今年的計算表試算之後,退稅反而是零。會不會是2019 Vanguard "VT" foreign source 標示為0%的關係,因為去年2018 VT foreign source 為64.44%。
    不過很奇怪的是我用 NEC退稅試算表Schwab_v4.20b9 計算我的2019嘉信帳戶,發現Taxable income 有減少,最後至少有退稅(比去年增加)。不知道是什麼原因?

    回覆刪除
    回覆
    1. 如果國際股市只有VT,那麼就是這樣。至於嘉信,也是只有VT嗎?如果不是那結果自然不同

      刪除
    2. 謝謝Rib大的回覆
      我的嘉信跟TD帳戶一樣有VT+BND(約8:2)。但經過試算表計算之後,嘉信有退稅率約59.14%,但TD卻是零。

      刪除
    3. 那是因為TD已經主動退給你了

      刪除
    4. TD有退BND沒錯,用試算表計算後:嘉信的VT還是可能退稅(超過5成),而TD的VT卻一毛也不能退,有點奇怪。未來VT如果要有退稅,可能要把TD的VT賣掉轉到嘉信了,或換成VXUS+VTI了!?

      刪除
    5. 沒道理這樣,因為稅務資料是一致的才對,如果不同年度當然又是另一回事

      刪除
    6. 我也覺得不可能會有這樣的問題。
      可能要請發問者提供VT是哪一年的退稅狀況。
      因為2018之前的VT是可退稅;而2019則無法退稅。
      而稅務資料是由Vanguard提供,不可能不同券商的退稅比率會有不同。

      刪除
  20. 請問Rib大,
    今年會更新稅務資料抽取試算表嗎?
    你提供這個功能實在太好用了~

    回覆刪除
    回覆
    1. 有計劃,但還沒開始。下週或許

      刪除
  21. Rib你好,今年是我第三次使用你提供的NEC來報報稅了,目前已經完成了資料的抽取,計算過後資料都很正確,越研究你提供的報稅表單,就越覺是博大精深阿,
    想請教的是你表格的部分是怎顯現出來的,是用程式寫出來的嗎??
    不知是否方便提供資你寫的資料,讓我能向你學習學習,

    這樣的請求,如有不妥,還請見諒

    感謝

    回覆刪除
    回覆
    1. 這完全就是用Google試算表的內建功能而已,並沒有寫任何程式。對Google試算表有興趣的話,歡迎一起學習討論。

      我沒有正式學過任何電腦程式,所需的知識面就是在多年Excel基礎下,加上Google試算表的線上說明就足夠了,不過,這試算表之所以能自動產生配息列表和報表,是依賴Google試算表提供了Excel沒有的篩選、查詢、排序、資料陣列函式,這些函式可以自動按預設參數完成Excel需要手動完成的資料處理,而這也是一般試算表使用者較少涉獵的部份。

      如果真要說有值得學習的部分,可能是報表生成的小技巧。但是,這個是我自己想到的偏方,對於真正會程式設計的人應該是不會這樣做的,因為這完全依賴資料本身的特性,並不具備普遍性。這裡就簡單介紹一下,有興趣再研究吧:

      其實試算表有個隱藏的工作表【報表組成】負責把計算結果轉換成報表的內容。由於主體的普通配息紀錄數目變異很大,所以在這裡先把普通股息紀錄放在後面,給予自由增長的空間。而記錄數變化比較少的長短期資本利得、退稅、利息、報表註解等資料,則放在前面預留的固定資料列,然後再設立一欄用以判斷每一列資料是否需要呈現。然後還有一欄用來設定個資料列在最終報表的位置。最後的報表內容,則是由【報表組成】的內容,篩選出需要顯示的資料列,再加以排序,就形成最後的報表。

      刪除
  22. Rib你好
    原來有隱藏工作表^^a,難怪我一直摸不清楚報表是怎麼產生,看你這麼一說,對你更是肅然起敬,太了不起了你,能把Google試算表應用到這樣的程度,謝謝你那麼詳盡的解說,
    ㄧ定要好好的來學習這傑作,誰敢說是偏方呢 (揍飛~~).
    再次感謝~~~~~~

    回覆刪除
  23. Rib你好,今年是我第四次使用你提供的NEC來報報稅了,目前已經完成了資料的抽取,計算過後資料都很正確,每年報稅,總是抱著感恩的心來使用這個試算表,想想當年的rib一定是花了不少心血,提供我們這麼好用的試算表~~~~

    另外請教一下 NOT(regexmatch(N1,BH21:BH&"$|"&BH21:BH&", "))
    這個是判斷什麼呢,看好幾次都還不理解說 ^^a

    謝謝~~

    回覆刪除
    回覆
    1. 具體的意義你要說一下出處,我也記不住那麼多。

      函式的話,regexmatch是字串比對函式,比對的語法是regular expression,搞懂語法才能弄懂。其實這試算表的線上說明還蠻完整的,有需要可以搜尋一下。

      https://support.google.com/docs/table/25273?visit_id=637830283299738848-3048346693&hl=zh-Hant&rd=2

      刪除
  24. rib你好,是計算 income Code 01+04 那裏,整段如下,看來要去了解 regular expression 謝謝

    =round(iferror(sum(filter(T3:T,Q3:Q=5)),0),0) + sum(iferror(filter(AA3:AA,Q3:Q=1,N(AY3:AY)<0.01),0))+sum(IFERROR(filter(BR3:BR15,BH3:BH15="Y",BU3:BU15>=0.01),0))+sum(IFERROR(filter(CE3:CE15,BH3:BH15<>"Y",BU3:BU15>=0.01),0)) -if(Upper(BF5)="YES", iferror(sum(filter( $BW$21:$BW-$BO$21:$BO/0.3,$BW$21:$BW>0,regexmatch(BN21:BN,"退還"),NOT(regexmatch(N1,BH21:BH&"$|"&BH21:BH&", ")))),0),0)

    回覆刪除
    回覆
    1. N1存放的是PTP distribution的ETF列表,regexmatch檢測N1的列表中是否有該列ETF名稱(位於BH21:BH)。由於N1是以","隔開ETF的代碼,所以用BH21:BH&", "來檢測,或者是位於列表末端後面沒有",",在regular expression是以"$"表示位於行末,所以BH21:BH&"$|",裡面的"|"則是"或"的意思,也就是以"|"分隔的條件只要滿足一個就可以

      刪除
  25. Rib你好,謝謝你淺顯易懂的說明,除了學會NEC的報稅外,又得到很多Gooogle 試算表的技巧
    ^^//

    回覆刪除
  26. 想請教:因為2022要報稅了,因此嘗試將2020年的此表單以2021年資料取代,但無法使用,(我用以值貼上)請問哪邊跑掉了呢?謝謝

    回覆刪除
    回覆
    1. 可能的原因太多,現在已經更新我想就不用管了

      刪除
  27. 請教rib大大,不知道這次的稅務抽取資料表是否有2022的Vangurad 和ishare的部份?因為在2021NEC不是也會用到2022/1/2-2022/3/15的部份,謝謝Rib大大的無私分享,這一路上有你的幫忙真的很幸運。

    回覆刪除
    回覆
    1. 2022的交易紀錄主要是涉及券商主動退還的預扣稅金紀錄,這部分不需要稅務資料。反而是前2021(2020)年年底的配息有時會在2022(2021)年初才入帳,所以以入帳日收入年分的TD Ameritrade有可能會需要2020年的稅務資料。大多數券商則是按派息日把2022(2021)年初才入帳的配息仍視為2021(2020)的收入,這些券商正常情況下都只需要2021的稅務資料。

      刪除
  28. 了解了,謝謝Rib大大的解說,真的很感謝你的無私分享,讓我們在辦理退稅的過程中不會無助孤單,真的萬分的感謝。

    回覆刪除
  29. Kim Yu 留下新留言:

    Rib大您好,
    看到之前留言的分享,知道TD把部分2021年的IEI配息歸到income code 06,因此試算表income code 01+04與code 06各自的1042-S與再分類差額相當。但最後的amount repaid to recipient卻也不小差異,不知是何故?想請rib 大幫忙,萬分感謝p.s.這是我用您的試算表退稅第5年了,非常感激有您這樣無私又厲害的人的存在!!祝福您~

    答覆

    下次請將試算表連結和問題內容分成2條留言,我才有辦法直接回覆。

    如果根據稅務資料可退稅之金額 (BD12) 和券商實際退還的稅金總和 (BD13) 不一致時,請檢查BO21以下的「手動」欄是否出現淺綠色格子,如果有,請從「尚未配對之退還紀錄」選取可配對的金額填入,才能正確完成比對,並將未完整的配息列於報表中加以解釋。如果兩者相符,則比對結果不會影響退稅計算,放著不管也無所謂

    你的情形就是把BQ21的數字填入BO22,就OK了。

    回覆刪除
  30. 有關2022 SPDR QII 的資料, 我早前下載了Google的"網頁庫存"來使用。剛剛已經分享給你。
    My google name is Cat Q Excel.

    回覆刪除
    回覆
    1. 我這邊並沒辦法從庫存頁面下載,檔案連結已經失效。

      刪除

求助時請將問題說明試算表分享連結或其他個人資訊分開成2個留言,以方便發表及回復求助說明的內容,並保留個人隱私不公開。