Tutorial Membuat Buku Investasi Sukuk (Obligasi) Menggunakan Excel
Untuk mempermudah pemahaman terhadap penjelasan di video, salin (copy) dan tempel (paste) rumus excel (function & formula) berikut pada sel yang sesuai.
Sheet "Sukuk", Baris 2 dan 4
- (F2) =SUM(F4:F1000)
- (G2) =SUM(G4:G1000)
- (H2) =G2-F2
- (C4) =IF(D4="B",RIGHT(E4,6),IF(D4="R",RIGHT(E4,6),IF(D4="Y",RIGHT(E4,6),"")))
- (D4) =IF(E4="Capital","C",IF(LEFT(E4,3)="Buy","B",IF(LEFT(E4,3)="Ret","R",IF(LEFT(E4,3)="Yie","Y",""))))
- (H4) =IF(B4="","",N(H3)-F4+G4)
Sheet "Portofolio", Baris 4
- (H4) =IF(G4="","",G4-(G4*15%))
- (J4) =IF(SUMIFS(Sukuk!F:F,Sukuk!C:C,B4,Sukuk!D:D,"B")-SUMIFS(Sukuk!G:G,Sukuk!C:C,B4,Sukuk!D:D,"R")=0,"",SUMIFS(Sukuk!F:F,Sukuk!C:C,B4,Sukuk!D:D,"B")-SUMIFS(Sukuk!G:G,Sukuk!C:C,B4,Sukuk!D:D,"R"))
- (K4) =IF(SUMIFS(Sukuk!F:F,Sukuk!C:C,B4,Sukuk!D:D,"B")=0,"",SUMIFS(Sukuk!F:F,Sukuk!C:C,B4,Sukuk!D:D,"B"))
- (L4) =IF(SUMIFS(Sukuk!G:G,Sukuk!C:C,B4,Sukuk!D:D,"Y")=0,"",SUMIFS(Sukuk!G:G,Sukuk!C:C,B4,Sukuk!D:D,"Y"))
- (M4) =IF(L4="","",L4/K4)
- (N4) =IF(K4="","",K4+L4)
Tutorial Membuat Buku Investasi Sukuk (Obligasi) Menggunakan Excel
No comments
Post a Comment