Formula Rumus Excel

Tingkat Keuntungan Sekuritas setelah diskon

Menghitung Tingkat Keuntungan Sekuritas yang Telah Didiskon

Kasus:

Suatu surat berharga memiliki data sebagai berikut:

TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT) : 11/1/2006 (1 Nop 2006)

TANGGAL JATUH TEMPO SEKURITAS (MATURITY) : 3/9/2007 (9 Maret 2007)

HARGA SEKURITAS PER NILAI NOMINAL 100 (PR) : 95

NILAI TEBUSAN (REDEMPTION) : 110

DASAR PERHITUNGAN BUNGA (BASIS) : 2 (actual/360)

Dari data tersebut hitunglah tingkat keuntungan tahunan sekuritas tersebut.

Penyelesaian:

Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja Ba4 pada lembar kerja KASUS21 seperti nampak pada gambar berikut:

/fc 3 !	^j L* ^fc	t-	J
4 !	PENERAPAN EXCEL PADA SURAT BERHARGA 1
5 > 6 7 l B <
9 *	MENGHITUNG TINGKAT KEUNTUNGAN YANG TELAH DIDISKON j
10 *
11 I	TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT}	01 November 2006
12 ;	TANGGAL JATUH TEMPO SEKURITAS (MATURITY)	09 Maret 2007
13	HARGA SEKURITAS PER NILAI NOMINAL 10O (PR)	95
14 ;	NILAI TEBUSAN (REDEMPTION}	110
15 :	DASAR PERHITUNGAN BUNGA (BASIS) Actual 3«»	2
16 !	TINGKAT KEUNTUNGAN TAHUNAN SEKURITAS	44.41%
17
1 If	► M\KARIIfi?1 /l<l i		H

Tingkat keuntungan tahunan sekuritas (FI6) dicari dengan fungsi YIELDDISC dengan langkah sebagai berikut:

1. Letakkan penunjuk sel pada alamat sel F16.

2. Aktifkan fungsi YIELDDISC (lihat bahasan fungsi-fungsi sebelumnya). Perhatikan gambar berikut:

Tunction Arguments
\| YIELDDISC ................. \|
Settlement }fi 1	39022
Matirfty \|F12	“ 39150
Pr )f13	^ - 9S
Redemption ]fi4
Basis \|fi5
* 0.444078947 Returns the amud yield for a discounted security. For example, a treasury M.
Settlement Is the secixity¹* settlement date, expressed as a serial date number.
j FornnJa res*Jt - 44.41%	\| OK 1 Cancel \|

3. Masukkan alamat sel sesuai dengan argumen pada masing-masing field tersebut sehingga akan nampak seperti gambar di atas (tampak sebagian).

4. Klik OK. Proses selesai.

Jika langkah Anda benar maka akan menghasilkan tingkat keuntungan tahunan sekuritas sebesar 44,41% seperti nampak pada gambar di atas dengan Menghitung Tingkat Keuntungan Sekuritas yang Telah Didiskon.

Tingkat Keuntungan Obligasi

Tingkat Keuntungan Obligasi

Kasus:

Suatu surat berharga, obligasi, memiliki data sebagai berikut:

TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT) : 10/14/2006( 140ktober 2006) TANGGAL JATUH TEMPO SEKURITAS (MATURITY) : 2/28/2007 (28 Februari 2007) NILAI NOMINAL SEKURITAS : 20.000

HARGA PASAR SEKURITAS : 18.000

Dari data tersebut hitunglah tingkat keuntungan tahunan sekuritas tersebut.

Penyelesaian:

Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja Bab4 pada lembar kerja KASUS 19 seperti nampak pada gambar berikut:

AE 3	C........ D E	F.......	—
4	PENERAPAN EXCEL PADA SURAT BERHARGA
5 6
7 i	TINGKAT KEUNTUNGAN SUATU OBLIGASI		_
8 :
9 ;	TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT)	14 Oktober 2006
10	TANGGAL JATUH TEMPO SEKURITAS (MATURITY)	28 Februari 2007
11 ;	NILAI NOMINAL SEKURITAS	20,000
12;	HARGA PASAR SEKURITAS	18,000
13 ;	TJNGKAT KEUNTUNGAN TAHUNAN (YIELD)	29.20%
14 4 4	►i\KASUS19/\|< j ..... I		ir

Tingkat keuntungan tahunan (FI3) dicari dengan menggunakan fungsi TBILLYIELD dengan langkah sebagai berikut:

1. Letakkan penunjuk sel pada alamat sel F13.

2. Aktifkan fungsi TBILLYIELD (lihat penjelasan fungsi-fungsi sebelumnya). Perhatikan gambar berikut:

Function Arguments
\| TBILL YIELD
Settlement \|F9[	3a
Maturity Jf10	51
Pr \|(F12/F11)*100	5J
Returns the yield for a Treasury bS.

3. Masukkan alamat sel sesuai dengan argumen pada masing-masing field tersebut sehingga akan nampak seperti gambar di atas.

4. Klik OK. Proses selesai.

Jika langkah Anda benar maka akan menghasilkan tingkat keuntungan tahunan sebesar 29,20% seperti nampak pada gambar di atas Tingkat Keuntungan Obligasi.

Harga Pasar Obligasi

Harga Pasar Obligasi

Kasus:

Suatu surat berharga - obligasi memiliki data sebagai berikut:

TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT) :l 1/1/2005(1 Nopember 2005) TANGGAL JATUH TEMPO SEKURITAS (MATURITY) : 2/28/2006 (28 Februari 2006)

DISCOUNT RATE SEKURITAS (DISCOUNT) : 9%

Dari data tersebut hitunglah harga pasar obligasi per nilai par 100.

Penyelesaian

Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja Bab4 pada lembar kerja KASUS18 seperti terlihat pada gambar berikut:

AB 3	UJ □ o	F	-
4	PENERAPAN EXCEL PADA SURAT BERHARGA i
5 l 6 j 7 !
8 '	HARGA PASAR SUATU OBLIGASI PER NILAI PAR 100
9
10 ;	TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT)	01 Nopember 2005
H	TANGGAL JATUH TEMPO SEKURITAS (MATURITY)	28 Februari 2006
12	DISCOUNT RATE SEKURITAS (DISCOUNT)	9.00%
I 3 ^:	HARGA PASAR OBLIGASI PER NILAI PAR 1 00	97.03
'⁴ -4 ►	►I \ KASUS 18 / j ^ 1 !	nr

Harga pasar obligasi per nilai Par 100 (F13) dicari dengan fungsi TBILLPRICE dengan langkah sebagai berikut:

1. Letakkan penunjuk sel pada alamat sel FI3.

2. Aktifkan fungsi TBILLPRICE (lihat penjelasan fungsi-fungsi sebelumnya). Perhatikan gambar berikut:

ction Arguments H^IH
TBILLPRICE....................................
Settlement \|f1G	■y - 336=7
Maturity jpil	31=³⁸⁷⁷⁶
Discount \|pl2	31= °-°⁹
Returns the price per $100 face value for a Treasury bi.	* 97.025
I Settlement is the Treasury biffs settlement date, expressed as a serial date number.
Formula result = 97.03
HelD on ths funrtion	1.....«.......1	Cancel

Perhitungan Bunga Aktual Excel

Perhitungan Bunga Aktual dengan Excel suatu surat berharga mempunyai data sebagai berikut:

TANGGAL PENERBITAN SEKURITAS (ISSUE) : 7 Jan 2006

PENERIMAAN PERTAMA BUNGA SEKURITAS (FIRST INTEREST) : 17 April 2006 TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT) : 20 Februari 2006

BUNGA SEKURITAS PER TAHUN (RATE) : 11%

NILAI PAR SEKURITAS (PAR) : 11.000

FREKUENSI PEMBAYARAN BUNGA PER TAHUN (FREQUENCY) : 2 kali setahun DASAR PERHITUNGAN BUNGA (BASIS) : 2 (Actual/360)

Dari data tersebut hitunglah bunga sekuritas dengan metode bunga aktual.

Penyelesaian:

Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja bab4 pada lembar kerja KASUS 1 seperti nampak pada gambar berikut:

AB 3	C ..................................D..................................................... E...............	F........	.........J
4	PENERAPAN EXCEL PADA SURAT BERHARGA
5
6 :	MENGHITUNG BUNGA SEKURITAS DENGAN METODE BUNGA AKTUAL		“
7 ;
8	TANGGAL PENERBITAN SEKURITAS (ISSUE)	07 Januari 2006
9	PENERIMAAN PERTAMA BUNGA SEKURITAS (FIRS INTEREST^-)	17 April 2006
10	TANGGAL PENYELESAIAN SEKURITAS (SETTLEMENT)	20 Februari 2006
11	BUNGA SEKURITAS PER TAHUN (RATE)	11%
12	NILAI PAR SEKURITAS (PAR)	11,000
13	FREKUENSI PEMBAYARAN BUNGA PER TAHUN (FREQUENCY)	2
14	DASAR PERHITUNGAN BUNGA (BASIS)/ Actual 360	2
15	BUNGA AKTUAL SEKURITAS	147.89
16 1 4 ►	►i Xkasusi/I <! .........................!		►if

Bunga aktual sekuritas (FI5) dicari dengan menggunakan fungsi ACCRINT dengan langkah sebagai berikut (ada 2 cara):

Cara 1 (satu):

Tempatkan penunjuk sel pada sel F15. Selanjutnya masukkan fungsi ACCRINT dengan menulis fungsi tersebut. Dalam kasus ini bentuk penulisannya adalah sebagai berikut:

=ACCRINT(F8,F9,F 10,F 11 ,F 12,F 13 ,F 14)

Cara 2 (dua):

Dengan mengaktifkan fungsi ACCRINT dengan prosedur sebagai berikut:

1. Letakkan penunj uk sel pada alamat sel F15.

2. Klik Insert, Function... Jendela Insert Funtion akan tampil.

3. Pilih kategori Financial pada field Or selecta category dilanjutkan dengan memilih pilihan ACCRINT pada field Select a function. Perhatikan gambar berikut:

4. Klik OK. Selanjutnya jendela Funtion Arguments akan tampil. Perhatikan gambar berikut (tampak sebagian):

5. Masukkan sel F8 (tanggal penerbitan sekuritas tertera) pada field Issue.

6. Masukkan sel F9 (penerimaan bunga pertama sekuritas tertera) pada field First _Interest.

7. Masukkan sel FIO (tanggal penyelesaian sekuritas tertera) pada field Settlement.

8. Masukkan sel FI 1 (bunga sekuritas per tahun tertera) pada field Rate.

9. Masukkan sel F12 (nilai par sekuritas tertera) pada field Par.

10. Masukkan sel F13 (frekuensi pembayaran bunga pertahun tertera) pada field Frequency.

11. Masukkan sel F14 (dasar perhitungan bunga tertera) pada field Basis.

12. Klik OK. Proses selesai

Jika langkah Anda benar maka akan menghasilkan bunga aktual sekuritas sebesar 147,89 seperti nampak pada gambar di atas.

Pada pembahasan kasus selanjutnya apabila tidak dijelaskan akan memakai cara kedua. Perbedaan mendasar hanya pada langkah ke-3 di mana pilihan Selecta Function-nya dipilih sesuai fungsi yang dikehendaki/terpilih. Barangkali anda mempunyai pertanyaan mengenaiPerhitungan Bunga Aktual Excel ini.

Fungsi XIRR Excel, tingkat bunga aliran kas masuk

Fungsi XIRR termasuk salah satu koleksi Analysis ToolPak. Fungsi XIRR (X Internal Rate ot Return) menghasilkan tingkat bunga yang dihasilkan dari suatu aliran kas masuk atau proceed (laba + penyusutan) yang diharapkan akan diterima karena terjadi pengeluaran modal (investasi). Fungsi ini hampir sama dengan fungsi IRR, dengan tambahan prediksi tanggal aliran kas. Fungsi XIRR mempunyai bentuk penulisan sebagai berikut:

=XIRR(values;dates;guess)

Di mana:

> Values diisi dengan range yang menunjukkan suatu aliran kas,

baik aliran kas keluar (investasi) maupun aliran kas masuk atau proceed.

> Dates range data yang berisi prediksi tanggal transaksi aliran

kas.

> Guess tingkat bunga yang disyaratkan. Jika diabaikan maka

dianggap sebesar 10%.

Kasus:

Suatu investasi mempunyai data sebagai berikut:

INVESTASI (2 Januari 2001) : 500.000.000 PERKIRAAN ALIRAN KAS MASUK

150.000.000

195.000.000

175.000.000 17%

TINGKAT SUKU BUNGA PENDANAAN

DATE 2 DATE 3 DATE 4 DATE 5 DATE 6

Dari data tersebut hitunglah tingkat suku bunga yang dihasilkan dari suatu aliran kas masuk yang diharapkan diterima karena investasi yang disertai dengan perkiraan tanggal tersebut.

Penyelesaian:

Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja Bab3 pada lembar kerja XIRR seperti nampak pada gambar berikut:

J-	8	G	D	E	F	G	H
2 .			PENERAPAN FUNGSI XIRR
3
4 5 6	MENGHITUNG TINGKAT BUNGA YANG DIHASILKAN DARI SUATU ALIRAN KAS MASUK ATAU PROCEED (LABA SETELAH PAJAK + PENYUSUTAN) YANG DIHARAPKAN AKAN DITERIMA KARENATERJADI INVESTASI YANG DISERTAI DENGAN PREDIKSI TANGGAL TRANSAKSI ALIRAN KAS.
7
8	IIIVESTASI	ALIRAN HAS MASUK					TJHGKAT SUKU BUNGA	_
9	IIIVESTASI	TAHUH KE-1	TAHUH KE-2	TAHUII KE-3	TAHUN KE-4	TAHUN KE-5	PENDANAAN
10 ^	VALUE	INCOME 1	INCOME 2	INCOME 3	INCOME 4	INCOME 5	RATE
11	-500,000,000	150,000,000	195,000,000	195,000,000	195,000,000	175,000,000	17.00%
12	02/01 £001	05/01/2002	18/02/2003	18/01/2004	29/01/2005	27/01 /2006
13		PREDKSI TANGGAL TRAIISAKSI ALIRAN KAS
14 15
16	X IIITERIIAL RATE OF RETURN <XIRR)			22M\
17 ; 4 4	► h\xirr/\|<!				J		►	If

X Internal Rate of Return (El6) diisi dengan fungsi sebagai berikut: =XIRR(B11:G11,B12:G12,H11)

Jika langkah Anda benar maka akan menghasilkan XIRR sebesar 22,66% seperti nampak pada gambar di atas dengan Fungsi XIRR.

Fungsi YIELD, menghitung tingkat keuntungan

Fungsi YIELD termasuk bagian dari Analysis ToolPak. Fungsi YIELD digunakan untuk menghitung tingkat keuntungan atau harapan dari surat berharga yang pembayaran bunganya dilakukan secara periodik. Fungsi ini mempunyai bentuk penulisan sebagai berikut:

=YIELD(settlement;maturity;rate;pr;redemption;frequency;basis)

Di mana:

> Settlement tanggal penyelesaian surat berharga.

> Maturity tanggal jatuh tempo sekuritas

> Investment nilai yang diinvestasikan pada surat berharga.

> Pr harga surat berharga per 100 nilai nominal.

nilai tebusan surat berharga per 100 nilai nominal.

menunjukkan frekuensi kupon obligasi. Untuk pembayaran tahunan (annual), frekuensi =1. tengah tahun (semi annual), frekuensi =2, dan kuartal, frekuensi = 4.

> Redemption

> Frequency

> Basis

jenis dasar penghitungan hari dalam setahun (lihat tabel pada penjelasan sebelumnya).

Kasus:

Suatu sekuritas mempunyai data sebagai berikut:

TANGGAL PENYELESAIAN SEKURITAS : I/18/2000 (18 Januari 2000)

: 2/18/2010 (18 Februari 2010) : 7%

BATAS WAKTU SEKURITAS TINGKAT SUKU BUNGA HARGA SEKURITAS PER NILAI NOMINAL 100 :90 NILAI TEBUSAN : 125

FREKUENSI PEMBAYARAN : 2

JUMLAH HARI DALAM SETAHUN : 3 (actual /365)

Dari data tersebut hitunglah tingkat keuntungan yang dapat diharapkan dari sekuritas tersebut.

Penyelesaian:

Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja Bab3 pada lembar kerja YIELD seperti nampak pada gambar berikut:

ti nampak pada gambar berikut:

A	B	c	D	..........E	F	0	H
2			PENERAPAN FUNGSI YIELD
3 ;
4 , 5 ^	MENGHITUNG TINGKAT KEUNTUNGAN ATAU HARAPAN DARI SURAT BERHARGA YANG PEMBAYARAN BUNGA DILAKUKAN SECARA PERIODIK.
6 :
	TANGGAL PBCYRESAIAM SBCWffMS	BATAS WAKTU SEKURITAS	TWGKATSUWJ BUIIGA	HARGA SEKURITAS PER MLAJ HOMMAL 1H	IUU TEBUSAN	FRB<UBISI PEMBAYARAN	JUMLAH HAM DALAM SFTAHUH
8 .	SETTLEMENT	MATURITY	RAW	«	REDEMPTION	FREQUB1CY	BASIS
		MS2*1#	7.1%	M	125	2
10 11
12	TNGKAT KEUNTUNGAN YANG DAPAT DHARAPKAN			10.09%
13: H <	► m\yield,	'J.....................			1		Ill

Tingkat keuntungan yang dapat diharapkan dari sekuritas (El2) diisi dengan fungsi sebagai berikut:

=YIELD(B9,C9,D9,E9,F9,G9,H9)

Jika langkah Anda di atas benar maka tingkat keuntungan yang dapat diharapkan dari sekuritas tersebut adalah sebesar 10,09% seperti nampak pada gambar tersebut di atas dengan Fungsi YIELD.

Fungsi XNPV menghitung selisih nilai sekarang investasi

Fungsi XNPV merupakan salah satu bagian dari Analysis ToolPak. Fungsi XNPV (X Net Present Value) digunakan untuk menghitung selisih nilai sekarang investasi (aliran kas keluar/cas/7 out) dengan nilai sekarang penerimaan bersih (aliran kas masuk/cos/? in) di waktu yang akan datang disertai dengan tanggal prediksi kejadian aliran kas. Fungsi ini hampir sama dengan fungsi NPV dengan mengikutkan tanggal. Fungsi XNPV mempunyai bentuk penulisan sebagai berikut:

=XNP V (rate; val ues; dates)

Di mana:

Rate tingkat suku bunga yang berlaku atau yang disyaratkan.

Kasus:

Dengan menggunakan data pada kasus XIRR di atas dengan perbedaan suku bunga adalah sebesar 16%, hitunglah selisih nilai sekarang investasi dengan nilai sekarang penerimaan bersih di waktu yang akan datang.

Penyelesaian:

Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja Bab3 pada lembar kerja XNPV seperti nampak pada gambar berikut:

A	B	..............c.........	..............D .........	......E.....	........ F	G	..........H	-
2 i			PENERAPAN FUNGSI XNPV
3
4 ;	MENGHITUNG SELISIH NILM SEKARANG INVESTASI DENGAN NILAI SEKARANG PENERIMAAN BERSIH
5 :			DI WAKTU YANG AKAN DATANG.
6
7 ;	TWGKAT SUKU	ALiRAII KAS
8	BUHGA	IIILAJ 1	IIILAI 2	IIILAI 3	IIILAI 4	IIILAI 5	IIILAI €
9 :	RATE	VALUE 1	VALUE 2	VALUE i	VALUE 4	VALUES	VALUE 6
10	16%	-500,000,000	150,(W0,000	195,000,000	195,COO,000	195,000,000	175,000,1X10
11		02*1/2001	05/01/2002	18/02/2003	1801/2004	29/01/2005	27/01/2006
12:			PREMKSJ TANGGAL TRAIISAKSI ALIRAII KAS
13 i 14 • 15	X NET PRESENT VALUE
16 17 1 4	► H\XNPV/\|	«f			I		►	f

X Net Presnt Value (D15) diisi dengan fungsi sebagai berikut: =XNPV(B10,C10:H10,C11:H11)

Jika langkah Anda benar maka akan menghasilkan XNPV sebesar 84.365.308 seperti nampak pada gambar di atas dengan Fungsi XNPV.

*~