2013年3月23日 星期六

1042-S 對帳試算表使用說明

(2013/3/30更新)

Firstrade 1042-S 對帳試算表 v2.0已經發表,計算更精確,使用更方便,還有更詳細的說明文件,歡迎使用。TD Ameritrade 2016年開始,1042-S的各類收入均四捨五入至整數位,導致進位誤差擴大,建議直接使用NEC退稅試算表對帳比較準確。

以下試算表是用來核對Firstrade和TD Ameritrade的帳戶記錄和報稅文件1042-S表格的內容是否相符,只要在試算表的第一個工作表貼上帳戶記錄,在第二個工作表的淺綠色欄位輸入報稅年度和1042-S對應的數字,就可以進行核對了。
對試算表不熟悉的朋友,下面"如何取得帳戶記錄"裡有更詳細的操作說明。對於計算結果怎麼核對如果不清楚,另見解讀美國券商報稅文件1042-S與帳目核對。對於試算結果有疑問,或想要近一步了解試算表的計算方式,本文後面有對技術細節的詳細解釋,不想接觸技術細節的朋友請忽略這個部分。

如何取得帳戶記錄

帳戶記錄的起迄日期必須涵蓋報稅年度全年和下個年度全部退稅記錄的全部帳目記錄。
  • Firstrade:退休與規劃>稅務中心>下載帳戶資信息>設定起迄日期>選擇下載格式Excel CSV
這個CSV檔案的日期是中文格式,下載的CSV檔案可以在Excel直接打開,要在Excel使用或貼入Google Docs都沒問題。如要在Google Docs直接打開CSV檔,要先確認雲端硬碟國別設成台灣(操作介面是繁體中文),才能正確轉換日期。
  • TD Ameritrade:Accounts>History & Statements > 設定起迄日期 > 先按 View 再按 Download
這個CSV檔案的日期是英文格式,如果在中文系統下用Excel打開的話,日期會當成文字處理,但貼進地區設定為英文的Google試算表時會正確轉換為日期資料。

如果是下載對帳試算表在Excel底下使用,則要先改變系統的日期格式:控制台>地區語言>格式>英文(美國),再用Excel直接打開,才能正確轉換為日期。

也可以將CSV檔上傳到Google雲端硬碟,由本試算表裡選擇檔案>匯入,其他用預設選項即可。如果要直接用Google Docs打開CSV檔的話,雲端硬碟國別要設成美國(操作介面是英文),才能正確轉換。

要確定日期轉換正確與否,可以觀察輸入後的試算表日期欄位的文字對齊方式,文字預設為靠左,日期預設為靠右,當然,要先把日期欄位拉到夠寬才看得清楚。

月結單對帳 (Firstrade)

當Firstrade 1042-S預扣稅總額與對帳結果不相符時,就表示CSV檔很可能有問題。

由於Firstrade帳目真的出錯的機率不高,主要是輸出CSV檔時發生錯誤。利用此工作表和月結單核對,找出錯誤的月份,再核對當月的各筆帳目即可找出錯誤。

使用方式

在綠色的格子內輸入要核對的年份和月份,就可以從匯入的CSV檔資料統計出當月 (This period) 和自年初開始累計 (Year to date) 的股息、預扣稅等,和月結單相對應的欄位核對後,就可以找出有錯誤的月份,再一筆筆核對帳目找出錯誤。

依我的經驗,錯誤主要發生在每年的一月份,所以可以先從兩個1月份的月結單查起。

要一筆筆核對帳目時,利用試算表的篩選功能挑出CSV記錄中Action為Dividend, Interest, Other三類帳目,會比較方便核對。

貨幣市場帳戶(標示為The Insured Deposit Program)利息和稅金退還交易也曾經漏列,通常貨幣市場帳戶利息每個月都會有ㄧ筆,利用篩選功能可以更快速的察覺錯誤。利用Firstrade網站的 "我的帳戶>帳戶紀錄" ,選擇適當顯示項目和日期區間,就可以找到正確的紀錄進行核對。

個人曾經多次向Firstrade反映CSV的錯誤,但始終只有事後更正或甚至沒有更正,而且一直沒有徹底弄出真正無錯的版本。如果大家都一起來找碴,也許Firstrade的技術部門才會真正徹底改善CSV的問題。

技術細節
注意!不想了解試算表技術細節的朋友以下內容可以忽略不看,以免徒增困惑。
如何調整計算方式達到正確計算效果

試算表是利用DSUM函數統計帳目裡的配息金額,灰色字的部分就是篩選條件。右側 "總共X筆" 則是同樣搜尋條件下,用DCOUNT函數統計符合的記錄數,這個數字有助於評估搜尋條件是否周延。

覺得有問題時,利用券商帳戶資料蒐尋不同交易類別的功能,得到的帳目筆數就可以和對應的搜尋條件核對。不過要注意,TD Ameritrade的貨幣基金利息是屬於Other而非Interest,而Firstrade的某些股息也會列為"其它" (似乎都是那些跨年分配的股息,不過不確定)

篩選條件適當與否是影響計算正確與否的主要因素。如果覺得篩選條件有問題,在Excel可以在帳試算表的第一個工作表 (帳戶記錄)使用資料>篩選的篩選功能測試篩選條件,直接找到正確的條件設定。

舊版Google Docs的篩選器功能不強,需要用Query函數達成類似Excel篩選的效果。

配息帳目格式解析

以下解析幾個比較不易設定和理解的篩選條件。
  • 股息
TD Ameritrade的格式很統一,都是分為2條:股利(ORDINARY DIVIDEND)、預扣稅(W-8 WITHHOLD),篩選條件應該很少有出錯的機會。

Firstrade的記錄方式很亂,目前看過的就有三種:
  1. 像TD那樣分2條 (NON-QUALIFIED DIVIDEND和NON-RES TAX WITHHELD)
  2. 只有1條稅後實際入帳金額的 (NON-QUALIFIED DIVIDEND NON-RES TAX WITHHELD),即已經扣掉預扣稅後的金額,而沒有明列原始配息金額和實際預扣稅金額。
  3. 少數 (如VWO)是列出全部股利(NON-QUALIFIED DIVIDEND),卻沒有再列預扣稅。
雖然在帳戶記錄倒是有完整的配息和預扣稅金額,但並沒有直接提供下載功能,也不利於我們拿來輸入試算表使用。所以只能用比較複雜的方式推算。目前的作法是:
  1. 總和A=全部含有NON-QUALIFIED DIVIDEND的記錄 (涵蓋前述三種狀況)
  2. 總和B=含有NON-QUALIFIED DIVIDEND NON-RES TAX WITHHELD的記錄 (狀況2)
  3. 原始配息總和=總和A+總和B*3/7
  4. 預扣稅總和=原始配息總和*30%
這樣的做法因為不是直接採用真正的帳目記錄,計算結果應該會和實際帳目有一點點誤差,但已經是目前我想到比較容易處理的方法了。

但問題不只如此。Firstrade還會在年初時還會出現幾筆跨年分配的股息,亦即入帳時間是該年度,但說明裡另外註明登錄為入前一年度的配息。例如標示為... REC 12/30/11 PAY 01/04/12...AMOUNT INCLUDED ON 2011-1099 ,即2012/1/4入帳,但會記錄為2011/12/30的配息。所以這幾筆配息和預扣稅必須挑出來扣除。(月結單的話,並不會列入2011/12,但會列入年度帳單,而2012/1的月結單則不包括這幾筆記錄)

這些狀況使得Firstrade十分複雜。更糟的是,Firstrade的CSV檔案內容不時會出錯,我的IEI, TIP, EMLC, EWZ等都曾經出現預扣稅金額錯誤 (目前為止都是發生在跨年的配息記錄),也曾莫名漏掉一筆退稅和一筆利息。我是每個月對帳的,比較能夠及時發現。如果一整年才核對這麼一次,一一核對會很辛苦,還是要設法把帳戶記錄的內容拷貝出來核對比較方便。但這部分就沒辦法從範例得到任何幫助,要熟悉試算表使用的人才能自己想辦法處理。
  • 利息
TD Ameritrade貨幣基金利息大多標為MONEY MARKET INTEREST (MMDAxx),金額是列在Quantity,即直接增加貨幣基金的單位數。另外還有一種OFF-CYCLE INTEREST (MMDAxx),其利息金額列於Amount,即先存入現金帳戶之後再轉為貨幣基金,這兩種應該都屬於Income Code 29。還有看過一種FREE BALANCE INTEREST,說明裡不帶MMDAxx,分類不明。用INTEREST為關鍵字可以找出全部利息,再區分是否有MMDAxx即可區分貨幣基金利息和其他利息,而加總時要同時計算Quantity和Amount的總和。

Firstrade的說明欄位內容不一,但用Action欄位標示為Interest就可以找出全部利息,再利用說明欄位配合萬用字元挑出貨幣基金利息的帳目。

其他配息的記錄都比較單純,只要看試算表內容應該就可以理解,這裡不再贅述。

18 則留言 :

  1. Rib您好,

    我嘗試使用Firtrade對帳試算表,發現下列問題。我可以順利轉換CSV檔,而每月對帳到2012.6月時有0.01的誤差(我想應該是四捨五入的問題),但對到9月時確發現月結單對帳B3及B7對不起來,我比對帳戶紀錄,發現9月份發的股息與B7欄相符,但就是找不出為何B3(THIS PERIOD,即9月)的計算會多了77.54。

    請問方便開連結請您協助確認嗎?如果不方便的話,也沒有關係。

    2.另有關TD Ameritrade我怎麼匯入CSV檔到Excel就是格式不對(所有都資料都是逗點分開,且集中在A欄位),請問還有其他方法讓格式正確嗎?

    回覆刪除
    回覆
    1. 1.應該是您願不願意的問題,因為那其實是個人隱私...老方法給我連結就行了。不過可能同時要有8,9,10三個月的月結單核對比較容易找出問題。記得要授權給我我才看得到。

      我很樂意多研究不同案例,因為我只是根據自己的帳目搞出這個試算表,如果能有更多人一起研究,就能讓試算表更家實用。

      2.請用"匯入外部資料"的方式匯入CSV,然選擇用逗點分欄,這樣就可以正確匯入了。別忘了注意日期格式有沒有正確轉換。

      刪除
    2. 知道藍色小熊在說甚麼了,牽涉個人資料的細節這裡不提(感謝藍色小熊的信任,已另外回覆),總之,試算表本身並沒有錯,帳目也是對的,B3=月結單數字,B7等於CSV帳目配息金額加總,但這兩者確實可能不同,請再仔細研究下正文裡"配息帳目格式解析"的部分已經有說明了。

      刪除
  2. Rib大,

    有關第二點TD匯入問題,蠻奇怪的。我改完系統日期格式後,直接在CSV檔按右鍵以Excel開啟,反而成功了、格式也對了。我也不知原因,僅供參考,還是很謝謝你。

    回覆刪除
    回覆
    1. 所以我那個建議行不通嗎?果然奇怪。謝謝您的分享。

      刪除
  3. Rib大,

    小的第一次報稅,看了很多您的分享,但還是有很多很基本的問題要跟您請教,不然總覺得好像沒有搞清楚。

    1. 我都是使用TD,這樣我下載帳目表,應該要設定2017/1/1~2017/12/31嗎?還是我時間期應該要怎麼抓才可抓到正確的資料呢?
    2. TD下載2017年的資料後,貼到您提供的excel對帳表即可?但我看分頁是寫2016對帳,所以是否2017的不適用呢? 且我貼上去,分頁都沒有動靜,不知道是哪個部分出錯了...

    謝謝Rib大!

    J

    回覆刪除
    回覆
    1. 2017/1/1到2018/3/31,因為現在限制一次不超過一年分,所以要分兩次抓。這試算表適用不同年份,要算哪年份的稅請自己手動改。

      刪除
  4. Hi Rib大,
    瞭解,我晚點再試試看,您說自己手動改,是指那個部分呢?謝謝!

    回覆刪除
    回覆
    1. 淺綠色欄位都是自己要填的。2016=>改成要報稅的年度,您的狀況是2017

      刪除
    2. Hi Rib大,
      因為是第一次收到1042-S表,所以今天又了您的試算表仔細比對、研究後,有碰到些問題想再跟您請教:

      1. 報稅是要看2017/01/01-2017/12/31期間內的收入,但資料要抓到2018/3/31,主要是要考慮「TD自動退稅」的部份嗎?
      2. 我目前透過TD買VTI/VEA/VWO/VNQ,但都沒有看到所謂「資本利得分配 (Capital gain distribution)」,這是這4支ETF的類型嗎?
      3. 所有券商的1042-S表都是無條件捨去嗎?還是是四捨五入呢?如果沒有小數位,為什麼TD給的1042-S還要寫例如「25.00」,不直接寫「25」?
      4. Rib大的試算表中,我的gross income實際加總與1042-S表box 2加總有1塊錢的落差(190 vs. 191),這是因為進位捨去所導致可忽略的差異嗎?
      5. 若想要累積2-3年再一起申報時,如果某年有跨兩個券商,這樣子申報上會不會更麻煩?
      6. 第一次的經驗,可能也是因為我買的ETF比較單純,加上又是TD出的表,所以感覺還算清楚(雖然還是花了很多時間了解),而在爬文過程中,也看到很多Firsttrade出1042-S表的問題,但現在有在考慮要在FT開戶,只是很怕到時候要花很多時間釐清FT的1042-S表,不知道這幾年FT的表有沒有比較"正常/合理"一些XD?

      謝謝Rib大!!!

      刪除
    3. 1. 是
      2. 基金有資本利得,也就是買賣獲利才有資本利得分配,像這種投資範圍大、資產規模大的指數型股票基金很少出現
      3. 目前都是四捨五入。列印格式你要問券商,我怎麼會知道。
      4. 一般很少進位問題會累積到這麼大的誤差,但也無法斷定。目前設計是不允許這麼大誤差,最好仔細檢查原因何在。雖然就退稅角度沒太大意義,但因為我們是自己另外重新分類,弄一個表數字對不上,總要想辦法找出原因才能確實交代。
      5.就是多做一個券商試算表再加一個加總報表,不算太麻煩。
      6.TD就偶而帳目回沖更整等造成CSV資料解讀問題,1042-S還沒聽說出錯的。而過去Firstrade的1042-S和CSV的錯誤根本沒有一致性和邏輯型,難以預測和觀察,而過去分享使用經驗的朋友很多後來就換券商了,回饋信息一年比一年少。現在有夠低的交易費和足夠給力的免交易費ETF名單,加上2018年開始沒有個人免稅額,或許會有更多Firstrade用戶改使用NEC退稅試算表,到時候才比較好說一般狀況。

      刪除
  5. Dear Rib大,

    1. 關於資本利得分配 (Capital gain distribution):所以是有哪些比較大型的ETF才會有短期跟長期資本利得呢?可否麻煩您舉個例子給我參考?
    2. 我是現在雲端複製一份對帳試算表,然後於google drive編輯後,才下載到電腦(xlsx檔),但我在下載下來後,打開會有錯誤訊息:因為某些內容無法讀取。問我是否要修復再開啟,即便我修復後,DSUM函數的地方資訊都跑不出來,這是因為檔案類型錯誤的關係嗎?
    3.感覺好像真的是進位問題耶,因為我加總算出來的一般股息是165.9,1042表分別是code 06($155)+code 37($10)=$165,就差了0.9(將近1塊錢),會有code 37我推測是因為VNQ有自動退稅,所以ROC改列code 37。不知道這樣子正不正確?


    謝謝Rib大!

    回覆刪除
    回覆
    1. 1. 會賣出部位的基金才有資本利得分配,由於ETF的特性,大型股票型ETF很少有,中期債券基金因為會賣出到期日超過投資範圍的債券,相對來講比較常見。或像某次暴跌導致大量變現需求的中國ETF,就導致大筆短期資本利得。

      2.檔案轉換有一定的機會發生錯誤,這我無力解決,您就線上用吧。

      3.這樣講我也無法確定。如果您願意可以直接用退稅試算表,那個比較精確。或者可以把試算表分享給我幫你看看。ROC再分類為37沒錯。

      刪除
  6. Rib大您好:

    沒有問題,可是我1042-S表要怎麼寄給您看呢?謝謝!

    回覆刪除
    回覆
    1. 可以把檔案上傳到自己的Google雲端硬碟,設定為知道連結可以讀取,然後留言留下連結就可以,我會注意不公開這個留言。這種溝通方式可以維持匿名。

      刪除
    2. 是進位誤差沒錯,由於現行TD的1042-S的各類收入是分別四捨五入的,所以誤差數字會放大,確實會出現比較大的誤差,所以這種1042-S還非得用NEC退位試算表才能正確對帳。

      刪除
  7. Rib大您好:感謝你協助核對,因為我看了很久都找不出問題,所以推測是進位及捨去的誤差導致,果然沒有錯!
    請問你說的NEC退位試算表可以在哪找到呢?


    回覆刪除
    回覆
    1. http://ribtw.blogspot.tw/2017/03/td-ameritrade-nec-2016.html

      刪除

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